Query to return the most recent rows of data

[SBLatta]

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.

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.

 

[MzKitty]

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