Get top 5 highest value columns using tsql

Question

Post reply

Get top 5 highest value columns using tsql

Sql Student-446896

Default port

Points: 1425
More actions
April 5, 2010 at 12:34 pm

#233270

Table
score_id, usernm, points
1 ABC 5
1 BBC
game 1 who are the top 5 ppl
game 2 who are top 5 ppl

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 1

You don't have a sample of your table here really so I will give you a sample.

-- Top 5 highest scores

SELECT TOP 5 GameNo, Score, Name, GameDate

FROM Games

WHERE GameNo = 1

ORDER BY Score DESC

-- Top 5 Lowest scores

SELECT TOP 5 GameNo, Score, Name, GameDate

FROM Games

WHERE GameNo = 1

ORDER BY Score

You can due TOP / BOTTOM percentages as well which would not be a specific record count, unless you included the TOP # records designation. BUT for what you want to do the simple query above would work.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Steve Jones - SSC Editor SSC Guru Points: 734816 More actions · Answer 2

Note that there is also a WITH TIES option.

Here's the BOL entry for TOP http://msdn.microsoft.com/en-us/library/ms189463.aspx

Sql Student-446896 Default port Points: 1425 More actions · Answer 3

Thanks a lot. I tried soemthing like this initally, but did not do order by. I did give a sample table (i has to edit initial post) ...dont know for some reason it did not post correctly.

Much Appreciated !

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 4

Sql Student-446896 (4/5/2010)
Thanks a lot. I tried soemthing like this initally, but did not do order by. I did give a sample table (i has to edit initial post) ...dont know for some reason it did not post correctly.
Much Appreciated !

Heh... nope... you gave us a bunch of text that we need to convert to test our answer to you with. Please read the article at the first link in my signature line below to see exactly what I mean and how you can get much better (and coded) answers much more quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)