Solved

# Top n from each parentID ?

Posted on 2009-05-15
Medium Priority
389 Views
Hi,

How can i select top n rows for each parentID, in MS SQL 2005 database
( this is very big table so i cant use functions, recursion etc, i think subquery will slow it down too)

Example table

parentID    someID   someOtherColums
1                   5
1                   6
1                   7
2                   8
2                   11
2                   23
3                   25
3                   20
4                   1
5                   3
5                   4
5                   31

now i need to select no more than 2 rows for each parentID, i will write my own logic wich 2 (first, last, some other filter, but lets asume i need first 2)

so results would look like:

1                   5
1                   6
2                   8
2                   11
3                   25
3                   20
4                   1
5                   3
5                   4

tnx
0
Question by:PapaStrumpf

LVL 57

Accepted Solution

Raja Jegan R earned 1400 total points
ID: 24393398
``````SELECT parentID,someID, someOtherColums
FROM (
SELECT parentID,someID, someOtherColums, ROW_NUMBER() OVER ( partition BY parentID ORDER BY someID) rnum
FROM urtable) temp
WHERE rnum <=2
``````
0

LVL 1

Expert Comment

ID: 24393414
You have to use cursors with counters. Fill the temporary table with cursor and select from it.
0

LVL 31

Expert Comment

ID: 24393499
>>You have to use cursors with counters. Fill the temporary table with cursor and select from it.<<

Author can't even use function, recursion and you are talking about CURSOR :)
0

LVL 31

Assisted Solution

RiteshShah earned 600 total points
ID: 24393546
if you want all records with tie than you can use, there is not much difference in the solution given by rrjegan17 and mine. there is just a little change.

``````SELECT parentID,someID, someOtherColums
FROM (
SELECT parentID,someID, someOtherColums, RANK() OVER ( partition BY parentID ORDER BY someID) rnum
FROM urtable) temp
WHERE rnum <=2
``````
0

Author Closing Comment

ID: 31581835
Tnx for help.
I split some points to RiteshShah becouse actually i will need that Rank thing, and i didnt know it exists.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.