INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query to return the most recent rows of data

Query to return the most recent rows of data

(OP)
Hi all. I'm trying to write a PSQL query in Crystal Reports which will pull only the most current data for each product from a sales history file. The data looks like this (only not sorted), and is contained in a table called ORLINH:

Custkey    Itemkey Location    OrdDate     UnitPrice

ABCCompany Widget1 Springfield  12/16/08   1.29
ABCCompany Widget1 Hollywood    12/15/08   1.35
ABCCompany Widget1 Springfield  12/07/08   1.20
XYZ Inc    Widget1 Atlanta      12/19/08   1.40
ABCCompany Widget1 Hollywood    11/30/08   1.32
XYZ Inc    Widget1 Atlanta      12/11/08   1.39

I have found plenty of examples of queries which "should" work if I only needed to aggregate on one field, but as you can see from the data, "Custkey", "Itemkey" & "Location" need to be combined in order to determine the last price a given customer was charged for an item from a given warehouse. I have tried using a subquery to identify the last OrdDate for a given combination, and that seems to work, but I can't find a way to pull the associated last UnitPrice.

CODE

SELECT "ORLINH"."Custkey", "ORLINH"."Itemkey", "ORLINH"."Location", Max("ORLINH"."Unitprice") as LastPrice,  Max("ORLINH"."Invdate") as LastInvoice
FROM   "ORLINH" "ORLINH"
WHERE "ORLINH"."INVDATE" =
(SELECT MAX("ORLINH"."INVDATE") AS Maxdate FROM "ORLINH" AS X WHERE X."Custkey"="ORLINH"."Custkey" AND X."Itemkey"="ORLINH"."Itemkey" AND X."Location"="ORLINH"."Location")
GROUP BY "ORLINH"."Itemkey", "ORLINH"."Location", "ORLINH"."Custkey"

I know Max("ORLINH"."UnitPrice") is wrong, as it is giving me the highest ever price for the Customer/Itemkey/Location combination rather than the most recent, but I had to put some kind of aggregate into the query for that field to make it run at all.

I am running Crystal Reports 10.0.5.820 and Pervasive SQL 9.5.
 

RE: Query to return the most recent rows of data

In crystal, go to the Database tab and select distinct.  Also most tables have a unique column, you might try to select max of the unique field associated with the key fields.  In the ARHIST table, the unique is the TransUniqueID.

Good Luck
Cathy  

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close