You are here: Re: Select first n rows of table « MsSQL Server « IT news, forums, messages
Re: Select first n rows of table

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]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация