Find second highest record in the table

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 2000 Forums

Transact-SQL (2000)

Find second highest record in the table

Author

Topic

harmeet
Starting Member

2 Posts

Posted - 2005-05-06 : 01:26:21

H,
I have a table

NAME SCORES
Harmeet 90
User_1 80
User_2 100

Query to select User having second highest record in the table.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-05-06 : 01:36:46

something like this:

create table #Table(Name varchar(200), Score int)

insert into #Table
select 'Harmeet', 90 union
select 'User_1', 80 union
select 'User_2', 100

select b.Name, b.Score
from (select top 1 name, score from (select top 2 name, score from #Table order by score) as a order by score desc) as b

Duane.

harmeet
Starting Member

2 Posts

Posted - 2005-05-06 : 01:52:04

ditch,
first thing I didnot want 2 use IN QUERY here as it impact performance
Second the OUTPUT should be like that

NAME SCORES
Harmeet 90

as "Harmeet" user has second highest score in the table

If there is no wind, row.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-05-06 : 01:57:12

Well to answer your second point.
The results are exactly as you asked - change the create table to use a varchar(20) as opposed to varchar(200) then you'll see - something like this:

Name Score
-------------------- -----------
Harmeet 90

it's cos the name is varchar(200) that the rest scrolls off the edge of the screen in QA.

and for your first point - how sure are you that it will have a heavy impact on the machine - have you tested it with huge amounts of data - have you looked at indexing. I reckon it should run pretty well.

Duane.

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2005-05-06 : 05:27:56

This may be the alternate for that query..

select top 1 * from
(select * from #Table
where score<(select max(score) from #Table)) as a

-VJ

Work smarter not harder take control of your life be a super achiever

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-06 : 08:53:54

also, you dont need to wrap it like you did.

select b.Name, b.Score
from (select top 1 name, score from (select top 2 name, score from #Table order by score) as a order by score desc) as b

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-05-06 : 09:05:43

mmmmmmm.......
OK - well i may be mistaken,
but this won't work.

select top 1 * from
(select * from #Table
where score<(select max(score) from #Table)) as a

cos there are 2 values less than the max(score) and cos you are using a Top 1 without an order by - you can never really be gauranteed of the order in which sql server returns the rows.

and the wrapping ( assume you are reffering to some kind of coding standards) - if I can receive a copy of the sqlteam.com tsql coding standards then won't make that mistake again :)

Duane.

RM
Yak Posting Veteran

65 Posts

Posted - 2005-05-06 : 10:37:03

What DonAtWork meant is ..

select top 1 name, score
from (select top 2 name, score
from #Table order by score) as a order by score desc

A select statement over this is unnecessary.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-05-06 : 12:26:39

I know that - I've got a weird sense of humour - Thats all.

[edit] it was early in the morning when I wrote that piece of code - that's my excuse anyways [/edit]
Duane.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-05-06 : 12:46:46

Heh. yes, that is what i meant

chetankelkar
Starting Member

5 Posts

Posted - 2005-05-08 : 01:54:52

Hi Harmeet,
Hey maybe I replied a too late. And also your query is a bit confusing as to :
1. You want the second highest* score
OR
2. Second top row.

In any case i have posted two queries, see if it helps.

For finding the second highest score in the table -->

SELECT TOP 1 *
FROM TEMPTBL1
WHERE SCORE <
(SELECT MAX(SCORE)
FROM TEMPTBL1)
ORDER BY SCORE DESC

This will give you second highest score in the table as desired.

For finding the second top row in the table -->
SELECT TOP 1 *
FROM TEMPTBL1
WHERE NAME <>
(SELECT TOP 1 NAME
FROM TEMPTBL1)

Hope this helps!

Brgds,
Chetan

quote:
Originally posted by harmeet

H,
I have a table

NAME SCORES
Harmeet 90
User_1 80
User_2 100

Query to select User having second highest record in the table.

Chetan Kelkar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-09 : 01:06:19

quote:
Originally posted by vijayakumar_svk

This may be the alternate for that query..

select top 1 * from
(select * from #Table
where score<(select max(score) from #Table)) as a

-VJ

Work smarter not harder take control of your life be a super achiever

This is enough

select max(score) from #Table
where score<(select max(score) from #Table)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources