how to get top 2 record from the table without using top

Question

0.00/5 (No votes)

See more:

Hello all,

I had asked a qns in Interview that If i Have a table with 3 fields and 3 records.

S_No Student_Name Student_marks
1 A 90
2 B 78
3 C 95

Now i want to select student name with Top 2 marks with out using TOP function.

Posted 8-Oct-12 1:59am

Neetesh Agarwal

Add a Solution

Comments

vivektiwari97701 8-Oct-12 8:07am

set rowcount 2

select column,column1 from tblEmployeeMaster

Neetesh Agarwal 8-Oct-12 8:59am

Thanks vivek.

vivektiwari97701 9-Oct-12 4:29am

u welcome

Neetesh Agarwal 9-Oct-12 23:36pm

its the sortest method I found..........

3 solutions

Solution 2

There are a couple options here. One is to use ROWCOUNT, as vivektiwari97701 pointed out. You can specify a ROWCOUNT of 2 and order by Student_marks in descending order like so:

SQL

SET ROWCOUNT 2

SELECT *
FROM tableName
ORDER BY Student_marks DESC

Don't forget to turn ROWCOUNT off afterwards, since it applies to all queries.

Another possibility would be to use the ROW_NUMBER() function in a CTE along like so:

SQL

With cteName AS
( SELECT S_No, Student_Name, Student_marks,
ROW_NUMBER() OVER (order by Student_marks DESC) as RowNumber 
FROM tableName)
select *
from cteName
Where RowNumber Between 1 and 3

The benefit here is that it only applies to this query and it can be changed if you made the numbers parameters. That would allow you to do paging easily. Also note that it is between 1 and 3, which will include 1 but not include 3 (thus records 1 and 2 only).

Posted 8-Oct-12 2:23am

Tim Corey

Comments

Neetesh Agarwal 8-Oct-12 8:59am

Thanks for Your answer

Neetesh Agarwal 9-Oct-12 23:30pm

But its also including 3............

Tim Corey 10-Oct-12 8:00am

The BETWEEN operator is treated differently in different databases. In SQL server, the 3 is usually not included. If it is being included, change the 3 to a 2.

Neetesh Agarwal 11-Oct-12 6:11am

Oh may be...........Thanks

Solution 3

I think following code can help you..

SQL

SELECT SNO,STUDENTNAME,MARKS FROM (
         SELECT SNO,STUDENTNAME,MARKS ,ROW_NUMBER()
         OVER(ORDER BY MARKS DESC) ROWNUM 
         FROM STUDENTMARKS
) As A WHERE A.ROWNUM < 3

Thank you

Posted 8-Oct-12 2:30am

damodara naidu betha

Updated 9-Oct-12 21:35pm

v2

Comments

Neetesh Agarwal 8-Oct-12 8:59am

Thanks

damodara naidu betha 8-Oct-12 9:09am

welcome

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)

Aarti Meswania · Accepted Answer · 2012-10-08T02:23:00

Solution 1

SQL

select s_no,Student_name,Student_marks from
(
select Row_number() over(order by Student_marks desc) as a,
s_no,Student_name,Student_marks from tbl
)as temptbl
where a<3

HAppy Coding!
:)

Posted 8-Oct-12 2:23am

Aarti Meswania

Comments

Neetesh Agarwal 8-Oct-12 8:59am

Thanks a lot

Aarti Meswania 8-Oct-12 9:00am

welcome! :)

Neetesh Agarwal 9-Oct-12 23:37pm

its working nicely

Aarti Meswania 10-Oct-12 0:32am

glad to help you! :)