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 |
|
Dennis Falls
Starting Member
41 Posts |
 Posted - 2011-02-02 : 13:40:51
|
| I have tblvisit with fields: date,county,complaint. I've been asked to query this for the top 5 complaints by county by year. I could do this manually, but since there are 100 counties over 4 years, you could imagine the time involved in running that many queries.If anyone has any ideas on how to automate this, it would be greatly appreciated. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-02-02 : 15:46:43
|
| Lookup row_number() in books online, possibly rank() or dense_rank() would work - depending on how you are calculating the top 5 complaints. Basically, you would use a query like:;WITH mydata (column list)AS (SELECT ... ,row_number() over(...) As rnFROM ...WHERE ...)SELECT ... FROM mydata WHERE rn <= 5;Jeff |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
complex data sort