Most Recent

G

Gary B

I am still having trouble with this...

OrderPriKey, CustForeignKey, OrderInfo

I want a query that will return just the most recent entry for each
CustForeignKey

The OrderPriKey is AutoInc, so the larger the number, the more
recent the entry.

OrderPriKey, CustForeignKey, OrderInfo
1, 1, Carrots
2, 1, Butter
3, 2, Onions
4, 2, Salt
5, 2, Pepper
6, 3, grapes
7, 3, milk

The proper result would be:
2, 1, Butter
5, 2, Pepper
7, 3, milk

Thank you!
 
C

Chris2

Gary B said:
I am still having trouble with this...

OrderPriKey, CustForeignKey, OrderInfo

I want a query that will return just the most recent entry for each
CustForeignKey

The OrderPriKey is AutoInc, so the larger the number, the more
recent the entry.

OrderPriKey, CustForeignKey, OrderInfo
1, 1, Carrots
2, 1, Butter
3, 2, Onions
4, 2, Salt
5, 2, Pepper
6, 3, grapes
7, 3, milk

The proper result would be:
2, 1, Butter
5, 2, Pepper
7, 3, milk

Thank you!
Click to expand...

Gary B.

Please forgive the dates appended to the table names.

Table:

CREATE TABLE Products_10292005_1
(OrderPriKey AUTOINCREMENT
,CustForeignKey INTEGER
,OrderInfo TEXT(10)
,CONSTRAINT pk_Products_10292005_1 PRIMARY KEY (OrderPriKey)
)

I Load the above sample data.

Query:

SELECT (SELECT P02.OrderPriKey
FROM Products_10292005_1 AS P02
WHERE P02.CustForeignKey = P1.CustForeignKey
AND P02.OrderInfo = P1.Orderinfo)
,P1.CustForeignKey
,P1.OrderInfo
FROM Products_10292005_1 AS P1
INNER JOIN
(SELECT MAX(P01.OrderPriKey) AS OrderPriKey
FROM Products_10292005_1 AS P01
GROUP BY P01.CustForeignKey) AS P2
ON P1.OrderPriKey = P2.OrderPriKey

Output:

2, 1, Butter
5, 2, Pepper
7, 3, milk


Sincerely,

Chris O.
 
Top