Beyondrelational

Tuesday, April 13, 2010

Select TOP First from Each Category

Need to write a query for selecting the record in a specific order,
The order is to select top first from each category and then top second from each category and so on…

The actual data is
nId GroupId

1 1
2 1
3 2
4 2
5 1
6 5
7 3
8 4
9 7
10 3
11 2
12 5
13 3
14 1
15 8
16 1
17 4
18 2
19 7

Need the output in the following order
nId GroupId
1 1
3 2
7 3
8 4
6 5
9 7
15 8
2 1
4 2
10 3
17 4
12 5
19 7
5 1
11 2
13 3
14 1
18 2
16 1

Suggest me some ideas to bring the desired output…
--------------------------------------------------------------------------

select nId,GroupId from(
select *,row_number()over(partition by GroupId order by nId) as rn from #table
)t
order by t.rn,t.GroupId,t.nId

No comments:

Post a Comment