selecting top N rows for each group in a table

by

If you’re using SQL Server 2005 or newer, you can use the ranking functions and a CTE to achieve this:

;WITH HairColors AS
(SELECT id, name, hair, score, 
        ROW_NUMBER() OVER(PARTITION BY hair ORDER BY score DESC) as 'RowNum'
)
SELECT id, name, hair, score
FROM HairColors
WHERE RowNum <= 3

This CTE will “partition” your data by the value of the hair column, and each partition is then order by score (descending) and gets a row number; the highest score for each partition is 1, then 2 etc.

So if you want to the TOP 3 of each group, select only those rows from the CTE that have a RowNum of 3 or less (1, 2, 3) –> there you go!

More Related Contents:

  1. Row Rank in a MySQL View
  2. Need to find list of tables used in stored procedure with “nolock”? [duplicate]
  3. Exclude a column using SELECT * [except columnA] FROM tableA?
  4. SQL/mysql – Select distinct/UNIQUE but return all columns?
  5. SQLite DateTime comparison
  6. Convert UTF-8 String Classic ASP to SQL Database
  7. Difference between LIKE and ~ in Postgres
  8. Multiple INNER JOIN SQL ACCESS
  9. Pivot on Multiple Columns using Tablefunc
  10. Multiple CTE in single query
  11. Selecting data into a Postgres array
  12. How to run a stored procedure in sql server every hour?
  13. How do I see active SQL Server connections?
  14. How to pivot rows into columns (custom pivoting)
  15. UPDATE if exists else INSERT in SQL Server 2008 [duplicate]
  16. Calculate the number of records for each date between 2 dates
  17. What is causing Foreign Key Mismatch error?
  18. T-SQL: How to use parameters in dynamic SQL?
  19. MySQL – how to front pad zip code with “0”?
  20. How to compare datetime with only date in SQL Server
  21. SQL server select distinct rows using most recent value only
  22. How can I implement SQL INTERSECT and MINUS operations in MS Access
  23. Split words with a capital letter in sql
  24. Access 2010: Syntax error (missing operator) in query expression
  25. Generate random int value from 3 to 6
  26. scope_identity vs ident_current
  27. PostgreSQL how to create a copy of a database or schema?
  28. Hamming Distance / Similarity searches in a database
  29. how to use LIKE with column name
  30. how to get the full resultset from SSMS

Leave a Comment