query to find Nth value explanation

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

General SQL Server Forums

New to SQL Server Programming

query to find Nth value explanation

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-20 : 09:19:26

can anyone explain how this works:

USE NORTHWIND

GO

SELECT freight
FROM orders E1
WHERE (N =
(SELECT COUNT(DISTINCT (E2.freight))
FROM orders E2
WHERE E2.freight >= E1.freight))

replace N by a number. To find that Nth value from the table.

Ashley Rhodes

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-20 : 10:33:40

try running this - it'll probably show you what is happening.

SELECT freight,
seq = (SELECT COUNT(DISTINCT (E2.freight))
FROM orders E2
WHERE E2.freight >= E1.freight))
FROM orders E1
order by seq

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

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-21 : 00:02:14

This is called co-related sub query.. based on each value of outer query the inner query will be processed.

As nr told put the where clause in the select list and check the result
also search this forum for more on nth value.

--------------------------------------------------
S.Ahamed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources