SQL Select the highest values ​​from a table on two (or more) columns

Question

Not sure if there is an elegant way to achieve this:

Data

ID Ver recID (loads more columns of stuff) 1 1 1 2 2 1 3 3 1 4 1 2 5 1 3 6 2 3 

So, we have an ID as the Primary Key, Ver as the version and recID as the record identifier (binary base identifier to bind all versions together).

So, I would like to select from the following data: lines 3, 4 and 6. that is, the highest version for this record identifier.

Is there a way to do this with a single SQL query? Or do I need to do a SELECT DISTINCT in the record ID and then a separate query to get the highest value? Or pull the lot into the application and filter from there?

Answer 1

A GROUP BY would be enough to get every maximum version for every recID .

 SELECT Ver = MAX(Ver), recID FROM YourTable GROUP BY recID 

If you also need the appropriate identifier, you can wrap it in a subquery

 SELECT yt.* FROM Yourtable yt INNER JOIN ( SELECT Ver = MAX(Ver), recID FROM YourTable GROUP BY recID ) ytm ON ytm.Ver = yt.Ver AND ytm.recID = yt.RecID 

or, depending on the version of SQL Server you are using, use ROW_NUMBER

 SELECT * FROM ( SELECT ID, Ver, recID , rn = ROW_NUMBER() OVER (PARTITION BY recID ORDER BY Ver DESC) FROM YourTable ) yt WHERE yt.rn = 1 

Answer 2

Getting the maximum ver for a given recID is simple. To get the ID , you need to join a subquery that gets these highs:

 select ID, ver, recID from table x inner join (select max(ver) as ver, recID from table group by recID) y on x.ver = y.ver and x.recID = y.recID 

Answer 3

You can use the cte function with ROW_NUMBER :

 WITH cte AS( SELECT ID, Ver, recID , ROW_NUMBER()OVER(PARTITION BY recID ORDER BY Ver DESC)as RowNum FROM data ) SELECT ID,Ver,recID FROM cte WHERE RowNum = 1 

Answer 4

straighforward example using a subquery:

 SELECT a.* FROM tab a WHERE ver = ( SELECT max(ver) FROM tab b WHERE b.recId = a.recId ) 

(Note: it is assumed that the combination (recId, ver) is unique. Usually for these columns in this order there must be a primary key or a unique constraint, and then this index can be used to optimize this query)

This works on almost all RDBMS sites, although the correlated subquery may not be processed very efficiently (depending on the RDBMS). In MS SQL 2008 will still work fine.