[RESOLVED] SQL SERVER 2005 - Top clause

[Ms.Longstocking]

Say I have 5 records in a table.

tableSample(username, color, total)

wendy blue 6
josh yellow 9
brad topaz 3
lily green 4
candy teal 11


now, I wish to select the user with the highest value.
In this case, I want Candy.

SELECT TOP 1 * from tableSample
order by total Desc

All good?


now, let's say that 2 or more users share the same top value?

wendy blue 6
josh yellow 9
brad topaz 11
lily green 4
candy teal 11


Would the following be the proper way to display brad and candy?:

DECLARE @highest tinyint
SET @highest = (SELECT MAX(total) from tableSample)
SELECT * FROM tableSample
WHERE total = @highest

[si_the_geek]

You don't need to create a variable, you can use the Max query directly as a sub-query:

SELECT * 
from tableSample
WHERE total = (SELECT Max(total) FROM tableSample)

[Ms.Longstocking]

I see that.
I simply wished to make sure that I wasn't overlooking something in SQL

Looks good.
Thanks si.