Microsoft SQL Server forum - Select first n rows of table

Author

Select first n rows of table

Andrew Lias

2006-03-28, 11:29 am

Let us say that I have a table with two sets of values as such:

Item Extension
--- ----
100023 1
100025 1
100025 2
100028 1
100029 1
100029 2
100029 3
[...]

Note that a given item number can appear multiple times if it has more
than one extension number.

I want to be able to select the first N entries as grouped by item
number. So if N = 3, that would return 100023, 100025 and 10028 with
their associated extentions. I would also like to be able to select
say, the 2nd through 9th entries grouped by item number.

I've tried something like this to give me row counts:

select rank = count(1), t1.item, t1.extension
from ItemTable t1 inner join itemTable t2
on t1.item >= t2.item
group by t1.item, t1.extension
order by rank

But that gives me this sort of result:

Rank Item Extension
---- --- ----
1 100023 1
3 100025 1
3 100025 2
4 100028 1
7 100029 1
7 100029 2
7 100029 3
[...]

Any suggestions would be welcome.

SQL

2006-03-28, 1:29 pm

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/

Andrew Lias

2006-03-30, 11:28 am

> also a temp table with identity is much faster than a running count
> Let me know if it works for you

Thanks. I was trying to avoid using a temp table, but this is a good
solution.

Sponsored Links