How To Retrive A Second Maximum Value in a column

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

SQL Server Development (2000)

How To Retrive A Second Maximum Value in a column

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2007-04-06 : 08:06:27

hi,
here i have a only one column in table which contain some numbers.
table name : tab1
column name : marks
marks
96
97
98
99
100.
here i need to retrieve second max value i.e 99.

so i written the query as:
select top2 * from marks oreder by(marks) desc intersect select max(marks) from marks.

the first part of query will be:
100
99
second part of query will be:
100.

so i thought if i intersect two outputs i will get 99.

but its showing error.

so please help to overcome the error in my query or show an alternate way.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-06 : 08:15:57

Are you using SQL 2000 or 2005 ? INTERSECT operator is for 2005


select marks 
from   (
            select top2 marks 
            from   marks 
            order by marks desc
        ) m 
intersect 
select max(marks) 
from   marks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-06 : 08:17:47

for SQL 2000 you can use


select	min(marks) from (select top 2 marks from marks order by marks desc) m

rammohan
Posting Yak Master

212 Posts

Posted - 2007-04-06 : 08:19:07

thank u khtan,

even if i written u r code its showing the error
"The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified."

pls show a solution

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

rammohan
Posting Yak Master

212 Posts

Posted - 2007-04-06 : 08:20:43

Thank u kthan its working

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-06 : 08:24:06

Should be top 2 and not top2
and if you want 99 you should use EXCEPT


select marks 
from   (
            select top 2 marks 
            from   marks 
            order by marks desc
        ) m 
intersect except
select max(marks) 
from   marks

rammohan
Posting Yak Master

212 Posts

Posted - 2007-04-06 : 08:26:40

thank u,
but u r second example is working properly, but the first example is not working now, its showing the error " Incorrect syntax near Intersect".

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-06 : 08:27:26

What version of SQL Server are you using ?

rammohan
Posting Yak Master

212 Posts

Posted - 2007-04-06 : 08:31:24

SQL SERVER 2000

select marks from (select top 2 marks from marks order by marks desc )m
except
select max(marks)
from marks

for above code also its showing error "Incorrect syntax near the keyword 'except'"

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-06 : 08:33:47

Intersect and Except operator is only available to SQL Server 2005 it is not available in 2000.

rammohan
Posting Yak Master

212 Posts

Posted - 2007-04-06 : 08:36:52

Ok. Thank you Kthan

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-07 : 02:18:53

quote:
Originally posted by khtan

for SQL 2000 you can use

select min(marks) from (select top 2 marks from marks order by marks desc) m

KH

My favourite

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-07 : 02:20:33

quote:
Originally posted by rammohan

SQL SERVER 2000

select marks from (select top 2 marks from marks order by marks desc )m
except
select max(marks)
from marks

for above code also its showing error "Incorrect syntax near the keyword 'except'"

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

You should read the replies clearly as Tan already specified that it will work only in sql server 2005

Madhivanan

Failing to plan is Planning to fail

svvanu
Starting Member

3 Posts

Posted - 2007-04-20 : 04:46:15

quote:
Originally posted by khtan

for SQL 2000 you can use

select min(marks) from (select top 2 marks from marks order by marks desc) m

KH

svvanu
Starting Member

3 Posts

Posted - 2007-04-20 : 04:48:42

select min(marks) from (select top 2 marks from marks order by marks desc) m

sir, here y u used m. please reply me sir.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-20 : 04:55:22

quote:
Originally posted by svvanu

select min(marks) from (select top 2 marks from marks order by marks desc) m

sir, here y u used m. please reply me sir.

Try removing 'm' and see what you get!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

svvanu
Starting Member

3 Posts

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

I tried but it shows error that's y i asked.. please explain y u used m.. i also used other alpha.. letters it ran proberly... repply

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-20 : 06:31:13

m is an alias used here to name a derived table. Basically any valid identifier can be used as an alias name and Yes!!...It is compulsory to provide an alias for derived table (otherwise how you are going to refer derived table columns??).

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dovesdale
Starting Member

11 Posts

Posted - 2007-04-28 : 04:23:48

SELECT MAX(num) AS Expr1
FROM A
WHERE (num NOT IN
(SELECT MAX(num)
FROM a))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources