Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-11 : 10:59:36
|
| Swaminathan writes "SQL Guru,How to select top 10 rows for each group.we have two fields id, pagesvisitedI want top 5 pagesvisited for each idThanksSwami" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-11 : 11:13:08
|
| try the following:/* setup script */create table #temp( id int, numpages int)insert #temp ( id, numpages ) values ( 1, 1 )insert #temp ( id, numpages ) values ( 1, 5 )insert #temp ( id, numpages ) values ( 1, 6 )insert #temp ( id, numpages ) values ( 1, 7 )insert #temp ( id, numpages ) values ( 1, 8 )insert #temp ( id, numpages ) values ( 1, 21 )insert #temp ( id, numpages ) values ( 1, 17 )insert #temp ( id, numpages ) values ( 1, 91 )insert #temp ( id, numpages ) values ( 1, 51 )insert #temp ( id, numpages ) values ( 2, 1 )insert #temp ( id, numpages ) values ( 2, 2 )insert #temp ( id, numpages ) values ( 2, 3 )-- select top 5 numpages for each idselect *from #temp twhere exists ( select id, count(*) as numgreater from #temp where id = t.id and numpages >= t.numpages group by id having count(*) <= 5) |
 |
|
|
|
|
|
|
|
Top 5 rows for each group