Find Nth Max - 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

Transact-SQL (2000)

Find Nth Max

Author

Topic

rajsequel
Starting Member

8 Posts

Posted - 2004-06-09 : 07:11:02

Hi , I need to select the nth value from a table which has no primary key index. No need to use any stored procedures, only using select queries even subquery need to achieve.

rajesh

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-09 : 10:35:00

I found this some time ago on this site somewhere....

Select *
From Test T1
Where (N-1) = (Select Count(Distinct(T1.FieldValue)) From Test T2 Where T2.FieldValue > T1.FieldValue)

N is Nth value you are looking for...

Enjoy!

Go with the flow & have fun! Else fight the flow :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-09 : 11:42:52

Didn't work


USE Northwind
GO

DECLARE @n int

SELECT @n = 5

SELECT OrderId
  FROM Orders T1
WHERE (@N-1) = (SELECT COUNT(DISTINCT(T1.OrderId)) 
		  FROM Orders T2 
		 WHERE T2.OrderId > T1.OrderId)
GO

I think you got it from here

http://www.sqlteam.com/item.asp?ItemID=16134

But I'm getting an error

quote:

Server: Msg 147, Level 16, State 2, Line 6
An aggregate may not appear in the WHERE clause unless
it is in a subquery contained in a HAVING clause or
a select list, and the column being aggregated
is an outer reference.

I wonder what I'm doing wrong?

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-09 : 11:46:31

this is the punch line: COUNT(DISTINCT(T2.OrderId) not COUNT(DISTINCT(T1.OrderId) :)

SELECT OrderId
FROM Orders T1
WHERE (@N-1) = (SELECT COUNT(DISTINCT(T2.OrderId))
FROM Orders T2
WHERE T2.OrderId > T1.OrderId)

originally my typo..

Go with the flow & have fun! Else fight the flow :)

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-09 : 12:05:44

Got it...

But that's a bad example on my part...the article is much better...

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-09 : 12:08:39

i agree the article is better, but i didn't feel like looking for it :) (today is so hot, my laziness index is way over the top :)))

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources