Getting Highest 5 values from database

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello All,
I have a table which stores the percentage of students. I wabt to get the topper list from it(getting highest 3 percentage acquired students).so which query will be better for it.actually i have tried max aand top functions but they are not suitable.max will give only one value and top will give only first 3 records from database.
so how to get highest 3 records.

Posted 22-Aug-10 23:09pm

amit2620

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nyarost · Answer 1 · 2010-08-22T23:10:00

Solution 1

SELECT TOP 5...

????

Posted 22-Aug-10 23:10pm

Nyarost

OriginalGriff · Answer 2 · 2010-08-22T23:13:00

Solution 2

Try:

SELECT <fields> FROM <tablename> WHERE <condition> LIMIT <startnumber>,<count> ORDER BY <field> ASC

Or (I hadn't noticed SQL2005 didn't support LIMIT):

SELECT Rank, ID, Percent FROM
    ( SELECT Rank = ROW_NUMBER() OVER (ORDER BY Percent), ID
      FROM myTable
    ) i 
WHERE 
    Rank BETWEEN 1 and 5 
ORDER BY 
    Rank

I haven't tested it, but it should work.

Posted 22-Aug-10 23:13pm

OriginalGriff

Updated 22-Aug-10 23:26pm

v2

Comments

Nyarost 23-Aug-10 5:17am

Limit won't work in SQLServer 2005

Nuri Ismail · Answer 3 · 2010-08-22T23:16:00

Solution 3

How about combining TOP with ORDER BY.
Pseudo code:

SQL

SELECT TOP 3 FROM your_table ORDER BY column_to_sort

Posted 22-Aug-10 23:16pm

Nuri Ismail

Baji Jabbar · Answer 4 · 2010-08-27T00:08:00

You can use Top 3 itself. First you need to sort the table rows using orderby , so the resulting table will be having the highest first, the second highest on second and so on. Now using Top 3 you can select the topmost 3 records.

SELECT TOP 3 * from( Select * from tablename ORDER By percntagefield )

It is not recommended to use the * , you can select the desired fields by mentioning its entity names

tarun_j200 · Answer 5 · 2010-08-26T23:28:00

Solution 4

Try This:

SQL

SELECT * FROM your_table t1 WHERE
3>=(SELECT COUNT(DISTINCT t2.column) FROM your_table t2 WHERE t2.column >= t1.column)

Posted 26-Aug-10 23:28pm

tarun_j200

Comments

Christian Graus 27-Aug-10 5:30am

Reason for my vote of 1
This is a far worse answer than the ones given four days ago.

tarun_j200 27-Aug-10 5:37am

Hi Christian!! Could you please tell me why you said this is worse answer??

Christian Graus 27-Aug-10 6:26am

Because it involves a subquery, which hurts performance, and because it is more convoluted than other examples given which will work perfectly well. SELECT TOP 5 FROM your_table ORDER BY column_to_sort was the best possible answer.

tarun_j200 27-Aug-10 6:38am

I added this query because it is generic to all databases whether its sql server, oracle or mysql.
There is no keyword like Top in oracle and mysql.