PapaStrumpf
asked on
Top n from each parentID ?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You have to use cursors with counters. Fill the temporary table with cursor and select from it.
>>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 :)
Author can't even use function, recursion and you are talking about CURSOR :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tnx for help.
I split some points to RiteshShah becouse actually i will need that Rank thing, and i didnt know it exists.
I split some points to RiteshShah becouse actually i will need that Rank thing, and i didnt know it exists.