CODEWITHSUNDEEP
sqlsql-server
neodymium
neodymium

Reputation: 3936

SQL Query group by highest revision number

I'm trying to write an SQL Server query to extract rows with the highest 'Revision' number for each distinct ColumnId.

For example if I have a data set such as:

Id  ColumnId  Revision
------------------------
1       1         1
2       1         2
3       1         3
4       2         1
5       2         2
6       2         3
7       2         4

My desired result is as follows:

Id  ColumnId  Revision
------------------------
3       1        3
7       2        4

I tried to use the following SQL statement:

SELECT Id, ColumnId, MAX(Revision) As Revision from Bug 
GROUP BY ColumnId

If I remove the Id from the query above it partially returns what I need. However I also need the Id column.

What am I missing here to get this query to work as expected?

EDIT: One thing I haven't made clear from the data set above is that highest Revision number for a particular ColumnId does not necessarily have the highest Id.

Id  ColumnId  Revision
------------------------
1       1         1
2       1         3 <- Note this has a higher revision number than row Id 3.
3       1         2 <- This has a lower revision number than row Id 2.
4       2         1
5       2         2
6       2         3
7       2         4

Upvotes: 1

Views: 3219

Answers (5)

MDiesel
MDiesel

Reputation: 2667

You could use a subquery and then inner join back on the ColumnId and the MaxRevision like this:

SELECT A.Id, A.ColumnId, A.Revision
from Bug A 
INNER JOIN
(SELECT ColumnId,  MAX(Revision) As MaxRevision 
FROM BUG
GROUP BY ColumnId ) B ON
A.ColumnId = B.ColumnId AND
A.Revision = B.MaxRevision

Upvotes: 5

Jesuraja
Jesuraja

Reputation: 3844

Try this:

SELECT    MAX(Id) AS ID, ColumnId, MAX(Revision) AS Revision
FROM      Bug 
GROUP BY  ColumnId

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

Try this:

SELECT b.Id, b.ColumnId, b.Revision from Bug b 
INNER JOIN
(SELECT ColumnID, MAX(Revision) as 'Revision' from Bug GROUP BY ColumnID) m
ON b.ColumnId = m.ColumnId AND b.Revision = m.Revision

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can get what you want in several ways. Here is a way using not exists that usually performs pretty well:

select id, columnid, revision
from bug b
where not exists (select 1
                  from bug b2
                  where b2.columnid = b.columnid and b2.revision > b.revision
                 );

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460138

You can use ROW_NUMBER, for example with a common-table-expression(CTE):

WITH CTE AS
(
    SELECT Id, ColumnId, Revision, 
           RN = ROW_NUMBER() OVER (PARTITION BY ColumnId ORDER BY Revision DESC)
    FROM Bug 
)
SELECT Id, ColumnId, Revision 
FROM CTE 
WHERE RN = 1

Demo

Upvotes: 3

Related Questions