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.
| 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. |
|
|
|
|
|
|
How to retrive the nth highest values.