CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Ashin
Ashin

Reputation: 139

SQL select top if columns are same

If I have a table like this:

Id  StateId Name
1   1   a
2   2   b
3   1   c
4   1   d
5   3   e
6   2   f

I want to select like below:

Id  StateId Name
4   1   d
5   3   e
6   2   f

For example, Ids 1,3,4 have stateid 1. So select row with max Id, i.e, 4.

Upvotes: 1

Views: 780

Answers (4)

David_001
David_001

Reputation: 5802

The following using a subquery, to find the maximum Id for each of the states. The WHERE clause then only includes rows with ids from that subquery.

SELECT 
    [Id], [StateID], [Name]
FROM 
    TABLENAME S1
WHERE 
    Id IN (SELECT MAX(Id) FROM TABLENAME S2 WHERE S2.StateID = S1.StateID)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

Disclaimer: I gave this answer before the OP had specified an actual database, and hence avoided using window functions. For a possibly more appropriate answer, see the reply by @Tanjim above.

Here is an option using joins which should work across most RDBMS.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT StateId, MAX(Id) AS Id
    FROM yourTable
    GROUP BY StateId
) t2
    ON t1.StateId = t2.StateId AND
       t1.Id      = t2.Id

Upvotes: 2

gofr1
gofr1

Reputation: 15977

You can use ROW_NUMBER() + TOP 1 WITH TIES:

SELECT TOP 1 WITH TIES
        Id, 
        StateId, 
        [Name]
FROM YourTable
ORDER BY ROW_NUMBER() OVER (PARTITION BY StateId ORDER BY Id DESC)

Output:

Id  StateId Name
4   1       d
6   2       f
5   3       e

Upvotes: 2

Esty
Esty

Reputation: 1912

; WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY STATEID ORDER BY ID DESC) AS RN
)SELECT ID, STATEID, NAME FROM CTE WHERE RN = 1

Upvotes: 8

Related Questions