CODEWITHSUNDEEP
sqlsql-server
chrysst
chrysst

Reputation: 357

SQL - Return records with highest version for each quotation ID

If I have a table with three columns like below

CREATE TABLE QUOTATIONS 
(
    ID INT NOT NULL,
    VERSION INT NOT NULL,
    PRICE FLOAT NOT NULL
);

In addition to this, lets say that the table consists of the follow records:

ID  |  VERSION  |  PRICE
----+-----------+--------
1   |     1     |   50
1   |     2     |   40
1   |     3     |   30
2   |     1     |   100
2   |     2     |   80
3   |     1     |   50

Is there any single SQL query that can be run and return the rows of all quotations with the highest version only?

The results should be like follow:

ID  |  VERSION  |  PRICE
----+-----------+--------
1   |     3     |   30
2   |     2     |   80
3   |     1     |   50

Upvotes: 0

Views: 41

Answers (2)

imomins
imomins

Reputation: 24

SELECT id,version,price
FROM tableName t
     JOIN (SELECT id,MAX(version) AS version 
           FROM tableName
           GROUP BY id) AS q1 ON q1.id = t.id AND q1.version = t.version

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I like this method which uses no subqueries:

select top (1) with ties q.*
from quotations q
order by row_number() over (partition by id order by version desc);

Basically, the row_number() assigns "1" to the highest version for each id. The top (1) with ties returns all the 1s.

Upvotes: 3

Related Questions