How to retrive the nth highest values.

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

Other Forums

Other Topics

How to retrive the nth highest values.

Author

Topic

vins
Starting Member

1 Post

Posted - 2011-12-01 : 03:31:43

How to retrive the nth highest values.without using functions

table name emp:
id
50
60
70
80
70

query:

SELECT a.id FROM emp a WHERE 2 = (SELECT COUNT(distinct b.id) FROM emp b WHERE a.id <= b.id) ;

results:

id
70
70

have analaysed subquery as below but did not get,kindly explain:

b.id >= a.id

50 >= 50 true
50 >= 60 false
50 >= 70 false
50 >= 80 false
50 >= 70 false

count=1
---------------

b.id >= a.id

60 >= 50 true
60 >= 60 true
60 >= 70 false
60 >= 80 false
60 >= 70 false

count=2
----------------

b.id >= a.id

70 >= 50 true
70 >= 60 true
70 >= 70 true
70 >= 80 false
70 >= 70 true

count=4
-----------------

b.id >= a.id

80 >= 50 true
80 >= 60 true
80 >= 70 true
80 >= 80 true
80 >= 70 true

count=5
-----------------

b.id >= a.id

70 >= 50 true
70 >= 60 true
70 >= 70 true
70 >= 80 false
70 >= 70 true

count=4
----------------

thanks in advance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-12-01 : 04:19:44

There aren't really any nice ways

You can persist a ranking with the data but then you have to keep that up to date.

Or you can do things like this:


DECLARE @topN INT = 5

SELECT TOP 1 topE.[ID]
FROM
    ( SELECT TOP (@topN) [ID] FROM emp ORDER BY [ID] ASC ) AS topE
ORDER BY
    topE.[ID] DESC

If you needed the N'th DISTINCT value (Though with a column named [ID] it should be distinct anyway) Then you put a distinct into the derived table.

Edit - multiple typos

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-12-01 : 06:08:43

Maybe

select id
from
(
select id, seq = rank() over (order by id desc) from tbl
) a
where seq = @rank

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources