I want to retrieve top 3 rows with equivalent numb

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

I want to retrieve top 3 rows with equivalent numb

Author

Topic

azharrahi
Starting Member

44 Posts

Posted - 2007-04-25 : 06:38:34

Hope you all will be fine
I have a problem like this.
A class of students have taken exams .
Now I want to select top 3 position holder getting highiest marks.
Now situation is that .. if there are two students getiing 100 marks, one is getting 99 marks and 1 is getting 98 marks, then tere are four students on top 3 positions
Student1 = 100 marks at first position
Student2 = 100 marks at first position
Student3 = 99 marks at second position
Student4 = 98 marks at thirs position

How can i get these four students for first three positions
If I use query
Select top 3 MonthlyPremium from insurant order by MonthlyPremium desc
then it reurns top 3 rows i.e.
Student1 = 100 marks (who is at first position)
Student2 = 100 marks (who is at first position)
Student3 = 99 marks (who is at second position)
while the student4 = 98 at third poistion is missing.
Can any body tell me the right query to select all these students.
Thanks

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 06:45:07

Use WITH TIES option along with TOP clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

azharrahi
Starting Member

44 Posts

Posted - 2007-04-25 : 06:51:48

Did you mean
Select top 3 with ties Marks from @Table order by Marks Desc
I also used it but got the same result as told above.

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

Nazim
A custom title

1408 Posts

Posted - 2007-04-25 : 07:48:23

try this.

select * from #test
where marks in
(
Select distinct top 3 marks from #test
order by marks desc)

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-25 : 07:51:36

If you use with ties, it check for the no.of records returned for example u hav 100, 99, 98, 100

if u put top 3 with ties then 100,100,99 u will not get 98.

pls try this

declare @tt table (col1 int)
insert @tt
select 100 union all
select 99 union all
select 98 union all
select 100 union all
select 99 union all
select 97

select col1 from @tt where col1 >= (
select min(col1) from @tt where col1 in(
select distinct top 3 col1 from @tt order by col1 desc)) order by col1 desc

if u want top 5 change the top 5 in the sub query

azharrahi
Starting Member

44 Posts

Posted - 2007-04-25 : 09:26:47

Thanks pbguy Your query has worked and resulted properly

Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources