CODEWITHSUNDEEP
sqlsqlite
John
John

Reputation: 35

SQL Statement intermediate issue

I have a table where lots of products are stored. Each row has 3 columns: ProductID, RevisionID, and Name

I need to make a sql statement to get a list of all the products, but only one for each ProductID.

However many rows can have the same ProductID but incrementing RevisionIDs.

So when getting all the products I need to make sure to only get one of each ProductID, and make sure them rows are the latest RevisionIDs.

SELECT * FROM #TABLE WHERE ProductID is unique AND this products RevisionID is the highest for all the ProductID rows

Upvotes: 0

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Use a join:

select t.*
from #table t join
     (select productid, max(revisionid) as maxri
      from #table
      group by productid
     ) tt
     on tt.productid = t.productid and tt.maxri = t.revisionid;

Upvotes: 3

Related Questions