CODEWITHSUNDEEP
sqlsql-server
chris_techno25
chris_techno25

Reputation: 2477

SELECT only latest record of an ID from given rows

I have this table shown below...How do I select only the latest data of the id based on changeno?

+----+--------------+------------+--------+
| id |  data   | changeno |    |
+----+--------------+------------+--------+
|  1 |   Yes   |     1    |    |
|  2 |   Yes   |     2    |    |
|  2 |  Maybe  |     3    |    |
|  3 |   Yes   |     4    |    |
|  3 |   Yes   |     5    |    |
|  3 |   No    |     6    |    |
|  4 |   No    |     7    |    |
|  5 |  Maybe  |     8    |    |
|  5 |   Yes   |     9    |    |
+----+---------+------------+-------------+

I would want this result...

+----+--------------+------------+--------+
| id |  data   | changeno |    |
+----+--------------+------------+--------+
|  1 |   Yes   |     1    |    |
|  2 |  Maybe  |     3    |    |
|  3 |   No    |     6    |    |
|  4 |   No    |     7    |    |
|  5 |   Yes   |     9    |    |
+----+---------+------------+-------------+

I currently have this SQL statement...

SELECT id, data, MAX(changeno) as changeno FROM Table1 GROUP BY id;

and clearly it doesn't return what I want. This should return an error because of the aggrerate function. If I added fields under the GROUP BY clause it works but it doesn't return what I want. The SQL statement is by far the closest I could think of. I'd appreciate it if anybody could help me on this. Thank you in advance :)

Upvotes: 0

Views: 177

Answers (4)

Gumilang
Gumilang

Reputation: 1

for other alternatives :

DECLARE @Table1 TABLE
(
 id INT, data VARCHAR(5), changeno INT
);

INSERT INTO @Table1
SELECT 1,'Yes',1
UNION ALL
SELECT 2,'Yes',2
UNION ALL
SELECT 2,'Maybe',3
UNION ALL
SELECT 3,'Yes',4
UNION ALL
SELECT 3,'Yes',5
UNION ALL
SELECT 3,'No',6
UNION ALL
SELECT 4,'No',7
UNION ALL
SELECT 5,'Maybe',8
UNION ALL
SELECT 5,'Yes',9

SELECT Y.id, Y.data, Y.changeno
FROM @Table1 Y
INNER JOIN (
    SELECT id, changeno = MAX(changeno) 
    FROM @Table1 
    GROUP BY id
) X ON X.id = Y.id 
WHERE X.changeno = Y.changeno
ORDER BY Y.id

Upvotes: 0

DhanushD
DhanushD

Reputation: 36

Not a well formed or performance optimized query but for small tasks it works fine.

SELECT * FROM TEST WHERE changeno IN (SELECT MAX(changeno) FROM TEST GROUP BY id)

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

This is typically referred to as the "greatest-n-per-group" problem. One way to solve this in SQL Server 2005 and higher is to use a CTE with a calculated ROW_NUMBER() based on the grouping of the id column, and sorting those by largest changeno first:

;WITH cte AS 
(
  SELECT id, data, changeno,
    rn = ROW_NUMBER() OVER (PARTITION BY id ORDER BY changeno DESC)
  FROM dbo.Table1
)
SELECT id, data, changeno
  FROM cte
  WHERE rn = 1
  ORDER BY id;

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You want to use row_number() for this:

select id, data, changeno
from (SELECT t.*,
             row_number() over (partition by id order by changeno desc) as seqnum
      FROM Table1 t
     ) t
where seqnum = 1;

Upvotes: 4

Related Questions