Get Second Highest Among all - SQL Server Forums

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)

Get Second Highest Among all

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-09-13 : 07:08:27

hey i want the second highest number or u can say field from my table and all the values of the row,,,corresponding to that second highest value
Thanks iN advance....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-13 : 13:52:26

[code]SELECT *
FROM YourTable
WHERE field IN
(SELECT TOP 1 field
FROM (SELECT TOP 2 field
FROM YourTable
ORDER BY field DESC)t
ORDER BY t.field)[/code]

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-09-15 : 00:31:43

Hey Thanks to uuuuuu,
I found a another method n my problem get solved .....

try this i think u found this good.....
this is for any highest record means N th record........

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)
Check it Out Guys,,,,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 01:39:03

quote:
Originally posted by ashishashish

Hey Thanks to uuuuuu,
I found a another method n my problem get solved .....

try this i think u found this good.....
this is for any highest record means N th record........

SELECT *
FROM Employee E1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(E2.Salary))
FROM Employee E2
WHERE E2.Salary > E1.Salary)
Check it Out Guys,,,,

When i tried above on a table of 97000 records it didnt complete it was still executing after 11 minutes and i stopped it. i tried below and it took only 114 ms.

SELECT TOP 1 * FROM
(SELECT TOP (N-1) * FROM Table
ORDER BY yourOrderField DESC)t
ORDER BY yourOrderField

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-09-15 : 02:39:07

Yea Thanks Man ,,,,,
really it working fast thanks 4 dat ???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 02:41:11

quote:
Originally posted by ashishashish

Yea Thanks Man ,,,,,
really it working fast thanks 4 dat ???

You're welcome

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-09-15 : 03:59:29

Yea I test ur query at one place now...where number of records in table 1325654 so it returns wrong results ,,,,
so i made some changes in my query so see that also ,,,,it takes 24 sec on this table....

SELECT top 1 *
FROM testtable E1
WHERE (3-1) = (
SELECT COUNT(DISTINCT(E2.marks))
FROM testtable E2
WHERE E2.marks > E1.marks)

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-09-15 : 03:59:53

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 04:02:12

quote:
Originally posted by ashishashish

Yea I test ur query at one place now...where number of records in table 1325654 so it returns wrong results ,,,,
so i made some changes in my query so see that also ,,,,it takes 24 sec on this table....

SELECT top 1 *
FROM testtable E1
WHERE (3-1) = (
SELECT COUNT(DISTINCT(E2.marks))
FROM testtable E2
WHERE E2.marks > E1.marks)

wrong results? can you give an example?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-15 : 04:17:29

Many methods
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2008-09-15 : 04:49:47

Thanks to u guys???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources