CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sandeep Thomas
Sandeep Thomas

Reputation: 4727

Top 3 rows based on score, but to handle ties

I've an SQL table with this structure

Stud Name     Score
---------------------
 Alex          7
 John         5.6
 Tom          8.2
 Antony        6
 George        9
 Mathew        7
 Jim          5.5

I need to find top three students based on score.

So my query is

select top 3 * from studtable order by score desc

But since there is a tie of score for Alex and mathew, we need to get both of them on the final result. So how can I handle this situation when a tie

Upvotes: 0

Views: 663

Answers (6)

Dan
Dan

Reputation: 125

select * from my_table where score in (select distinct score from my_table order by score desc limit 3) order by score desc;

Upvotes: 0

Amar Kate
Amar Kate

Reputation: 71

You can consider using group by before order by //this will improve performance as well 

select top 3 * from studtable group by score order by score desc

Upvotes: 0

Cris
Cris

Reputation: 13341

You can try using subquery

select * from studtable 
where score in (select distinct top(3) score from studtable order by score desc)
order by score desc

Upvotes: 0

vhadalgi
vhadalgi

Reputation: 7189

Try this:

select top 3 with ties * from studtable order by score desc

This'll get you top 3 including ties.

See it in Action

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8402

select STA.* 
from studtable STA
inner join (SELECT Top 3 Score from studtable ORDER BY Score DESC) STB
on STA.Score = STB.Score
 order by STA.score desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can use comparisons in the where clause. Something like this should work:

select top 3 *
from studtable
where score in (select top 3 score from studtable order by score desc)
order by score desc;

This returns all rows with the same score as any in the top 3, which is, I think, what you want. If you want the top 3 distinct scores:

where score in (select top 3 score from studtable group by score order by score desc)

An alternative approach is to use ranking functions:

select st.*
from (select st.*, dense_rank() over (order by score desc) as seqnum
      from studtable
     ) st
where seqnum <= 3;

Upvotes: 1

Related Questions