CODEWITHSUNDEEP
sqlsql-server
intrigued_66
intrigued_66

Reputation: 17268

Does this code return the latest date PER product?

I wish to get the latest date per product ID:

SELECT ProductID, Max(Date) as Date FROM MyTable GROUP BY ProductID

I was always under the impression I had to do a nested join, but this appears to work!

EDIT: Thanks for all replies

Upvotes: 0

Views: 80

Answers (4)

Ɖiamond ǤeezeƦ
Ɖiamond ǤeezeƦ

Reputation: 3331

It is correct. You would need to use a nested JOIN if you were wanting to retrieve other columns in addition to the ProductID columns from the table. E.g:

SELECT ProductID, Quantity, ...
FROM MyTable
JOIN
(
    SELECT ProductID, Max(Date) AS Date 
    FROM MyTable 
    GROUP BY ProductID
) T1 ON MyTable.ProductID = T1.ProductID AND MyTable.Date = T1.Date;

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21776

Yes, per ProductId being exact. If it represents Product and there exists Date for this product - then completely YES

Upvotes: 1

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174427

It is correct. It returns the biggest value of the column Date of all rows with the same ProductID.

Upvotes: 1

JNK
JNK

Reputation: 65197

That's what aggregates do!

For each unique value of ProductId, return the maximum Date value. Not nested JOIN is necessary.

Upvotes: 2

Related Questions