CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
Sven Grosen
Sven Grosen

Reputation: 5636

Select Different Column Value for Row with Max Value

I'm hoping for a cleaner way to do something that I know how to do one way. I want to retrieve the UserId for the MAX ID value as well as that MAX ID value. Let's say I have a table with data like this:

ID  UserId  Value  
1   10      'Foo'  
2   15      'Blah'  
3   10      'Blech'  
4   20      'Qwerty'

I want to retrieve: 

ID   UserId  
4    20

I know I could do this like so:

SELECT
 t.ID,
 t.UserID
FROM
(
 SELECT MAX(ID) as [MaxID]
 FROM table
) as m
JOIN table as t ON m.MaxID = t.ID

I'm only vaguely familiar with the ROW_NUMBER(), RANK() and other similar methods and I can't help believing that this scenario could benefit from some such method to get rid of joining back to the table.

Upvotes: 1

Views: 1788

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44336

SELECT TOP 1 ID, UserID FROM <table> ORDER BY ID DESC

Upvotes: 1

steoleary
steoleary

Reputation: 9298

You could also just do it with a sub-query like this:

SELECT  ID ,
        UserID
FROM    table
WHERE   ID = ( SELECT   MAX(ID)
               FROM     table
             );

Upvotes: 1

Ian Preston
Ian Preston

Reputation: 39586

You can definitely use ROW_NUMBER for something like this:

with t1Rank as
(
  select *
    , t1Rank = row_number() over (order by ID desc)
  from t1 
)
select ID, UserID
from t1Rank
where t1Rank = 1

SQL Fiddle with demo.

The advantage with this approach is you can bring Value (or other fields as required) into the result set, too. Plus you can tweak the ordering/grouping as required.

Upvotes: 1

Related Questions