Geek Answers Handbook

SQL query to return the top N rows to an identifier for a variety of identifiers

Let's say I have a table containing several hundred million rows that looks something like this:

memID | foo  | bar  | foobar
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
.
.
.
10001 | blah | blah | blah
10001 | blah | blah | blah

I need a query that will return the top N rows for each memID in the range of member IDs. For example, if N = 3 and the range is 0-2, it should return

memID | foo  | bar  | foobar
1     | blah | blah | blah
1     | blah | blah | blah
1     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah
2     | blah | blah | blah

I looked at a couple of approaches, first creating a massive

SELECT TOP (3) *
FROM table
WHERE memID = 0
UNION ALL
SELECT TOP (3) *
FROM table
WHERE memID = 1
.
.
.

request in code. This is not a realistic option, probably for obvious reasons.

The second approach is to create a temporary table and a loop over the range of memIDs that insert TOP 3 for each memID into the temporary table.

WHILE @MemID < 10000 AND @MemID > 0
  BEGIN
    INSERT INTO tmp_Table
    SELECT TOP (3) *
     FROM table
     WHERE memID = @MemID

    SET @MemID = @MemID + 1
    END

This works, but I wonder if there is a more elegant solution for a single request that I am missing.

, , , , , .

+2
5
declare @startID int, @endID int, @rowsEach int
select @startID = 0, @endID = 2, @rowsEach = 3


select *
from
(
    select memID, foo, bar, foobar, row_number() over (partition by dense_rank order by dense_rank) [rank_row]
    from
    (
        select memID, foo, bar, foobar, dense_rank() over (order by memID) [dense_rank]
        from #test
        where memID between @startID and @endID
    ) a
) b
where rank_row <= @rowsEach

:

memID       foo  bar  foobar rank_row
----------- ---- ---- ------ --------------------
1           blah blah blah   1
1           blah blah blah   2
1           blah blah blah   3
2           blah blah blah   1
2           blah blah blah   2
2           blah blah blah   3

, :

create table #test
(
      memID     int not null
    , foo       char(4) not null
    , bar       char(4) not null
    , foobar    char(4) not null
)

insert into #test (memID, foo, bar, foobar)
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 1, 'blah', 'blah', 'blah'
union all
select 2, 'blah', 'blah', 'blah'
union all
select 2, 'blah', 'blah', 'blah'
union all
select 2, 'blah', 'blah', 'blah'
union all
select 2, 'blah', 'blah', 'blah'
union all
select 10001, 'blah', 'blah', 'blah'
union all
select 10001, 'blah', 'blah', 'blah'
union all
select 10001, 'blah', 'blah', 'blah'
+5
SQL> select ename,sal,
  2   row_number()
  3     over (order by sal desc)rn,
  4   rank()
  5     over (order by sal desc)rnk,
  6   dense_rank()
  7     over (order by sal desc)drnk
  8   from emp
  9  order by sal desc
 10  /

ENAME    SAL   RN   RNK   DRNK
-----   ----   --   ---   ----
 KING   5000    1     1      1
 FORD   3000    2     2      2
SCOTT   3000    3     2      2
JONES   2975    4     4      3
BLAKE   2850    5     5      4
CLARK   2450    6     6      5
+2

SQL Server 2005 2008,

+1

. , :

SELECT memID, foo, bar, foobar 
FROM  (
       SELECT memID, foo, bar, foobar, 
              RANK() OVER (PARTITION BY memID ORDER BY memID) AS 'nRank'
       FROM   table
       WHERE  memID BETWEEN 0 AND 2)
WHERE  nRank <= 3
+1
SELECT * FROM Member m
Join ( Select TOP(3) * From Table Order By Table.Id) as t 
    On t.MemberId = m.MemberId
Where m BETWEEN 0 and 10000

0

More articles:


All Articles