Getting the corresponding primary key from MAX()??

microsoft.public.sqlserver.programming

17 Dec 2005 6:35 PM

Greg

I think this should be easy, but maybe my brain is just not working today,
can anyone offer suggestions?

I have a table, Table1 (simplified):

ID      dollars
1            15
2            30
4            22

Using T-SQL, how can I get the ID value for the record with the highest
number in the dollars column?

"Select MAX(dollars) from Table1" gives me the actual highest value, but I
need to know the record from where that value came from.

Any thoughts?

17 Dec 2005 6:45 PM

markc600

select ID from Table1 where dollars=(Select MAX(dollars) from Table1)

Note that unless there is a unique constraint on the dollars column,
you may get multiple rows back.

18 Dec 2005 8:32 PM

Greg

That's what I needed...thanks.

<markc***@hotmail.com> wrote in message
Show quote

news:1134845143.777978.193910@g14g2000cwa.googlegroups.com...
>
> select ID from Table1 where dollars=(Select MAX(dollars) from Table1)
>
> Note that unless there is a unique constraint on the dollars column,
> you may get multiple rows back.
>