|
Posted by SQL on 10/01/23 11:43
Here are 2 ways distinct and using group by
It's using a temp table with the identity function
The problem that you have is that you implemented a DENSE_RANK instead
of RANK
In SQL Server 2005 this is much easier of course
CREATE TABLE Rankings (Item int , Extension int)
INSERT INTO Rankings
SELECT 100023,1 UNION ALL
SELECT 100025,1 UNION ALL
SELECT 100025,2 UNION ALL
SELECT 100028,1 UNION ALL
SELECT 100029,1 UNION ALL
SELECT 100029,2 UNION ALL
SELECT 100029,3
SELECT IDENTITY(INT, 1,1) AS Rank ,Item,Extension
INTO #Ranks FROM Rankings WHERE 1=0
INSERT INTO #Ranks
SELECT Item,Extension FROM Rankings
ORDER BY Item,Extension
declare @top int
select @top=3 -- this would be you top n...
SELECT distinct z.Ranking ,t2.Item,r.Extension
FROM (SELECT (SELECT COUNT( DISTINCT t1.Item) FROM Rankings t1 WHERE
z.Item>= t1.Item)AS Ranking, z.Item
FROM #Ranks z ) z
JOIN #Ranks t2 ON z.Item = t2.Item
join Rankings r on r.Item = z.item
and r.Item = t2.Item
and ranking <=@top
ORDER BY z.Ranking
also a temp table with identity is much faster than a running count
Let me know if it works for you
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Navigation:
[Reply to this message]
|