finding the nth max of count(column)

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 2005 Forums

Transact-SQL (2005)

finding the nth max of count(column)

Author

Topic

mvssrikanth1
Starting Member

4 Posts

Posted - 2007-12-21 : 08:47:14

I have a table "product" with a field "productid".
First,I have to calulate the count(productid).
Next, I have to calculate the nth max of (count(productid)).
can anyone help me in this regard in writing the t-sql for this problem.
Thanks ,
srikanthMVS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-21 : 08:50:42

can you post some sample data and expected result ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-21 : 09:08:25

DECLARE @Stage (RowID INT IDENTITY(1,1), ProductID INT, Items INT)

INSERT @Stage (ProductID, Items)
SELECT ProductID, COUNT(*) FROM Product GROUP BY ProductID ORDER BY COUNT(*) DESC, ProductID

SELECT ProductID, Items FROM @Stage WHERE RowID = @NthPlace

E 12°55'05.25"
N 56°04'39.16"

mvssrikanth1
Starting Member

4 Posts

Posted - 2007-12-21 : 09:15:05

If the table product has productid column with values "1001,1001,1002,1003,1003,1003,1003".
count(1001)=2,count(1002)=1,count(1003)=4
maximum of count(productid) is 4
second maximum of count(productid) is 2
minimum of count(productid) is 1.

like this, i have to find the nth max of count(productid).

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-21 : 09:24:21

[code]SELECT productid, cnt
FROM
(
SELECT productid,
cnt = COUNT(*),
row_no = row_number() OVER (ORDER BY COUNT(*) DESC)
FROM product
GROUP BY productid
) a
WHERE row_no = @n[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-21 : 10:24:23

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources