Problem
In one query, I’d like to get the top 10 records from each sector. Is there anyone who can show me how to do it? Section is one of the table’s columns.
SQL Server 2005 is the database. I’d want to get the top ten results based on the date entered. There are three sections: business, local, and feature. I just want the top (10) company rows (most recent entry), the top (10) local rows, and the top (10) features for a specific date.
Asked by jbcedge
Solution #1
You can do something like this if you’re using SQL 2005…
SELECT rs.Field1,rs.Field2
FROM (
SELECT Field1,Field2, Rank()
over (Partition BY Section
ORDER BY RankCriteria DESC ) AS Rank
FROM table
) rs WHERE Rank <= 10
You may return more than 10 rows if your RankCriteria includes ties, and Matt’s solution may be better for you.
Answered by Darrel Miller
Solution #2
In T-SQL, I’d write:
WITH TOPTEN AS (
SELECT *, ROW_NUMBER()
over (
PARTITION BY [group_by_field]
order by [prioritise_field]
) AS RowNo
FROM [table_name]
)
SELECT * FROM TOPTEN WHERE RowNo <= 10
Answered by Phil Rabbitt
Solution #3
SELECT r.*
FROM
(
SELECT
r.*,
ROW_NUMBER() OVER(PARTITION BY r.[SectionID]
ORDER BY r.[DateEntered] DESC) rn
FROM [Records] r
) r
WHERE r.rn <= 10
ORDER BY r.[DateEntered] DESC
Answered by lorond
Solution #4
This works with SQL Server 2005 (added your clarification):
select *
from Things t
where t.ThingID in (
select top 10 ThingID
from Things tt
where tt.Section = t.Section and tt.ThingDate = @Date
order by tt.DateEntered desc
)
and t.ThingDate = @Date
order by Section, DateEntered desc
Answered by Matt Hamilton
Solution #5
This is how I do it:
SELECT a.* FROM articles AS a
LEFT JOIN articles AS a2
ON a.section = a2.section AND a.article_date <= a2.article_date
GROUP BY a.article_id
HAVING COUNT(*) <= 10;
update: This GROUP BY example only works with MySQL and SQLite, because both databases allow greater flexibility with GROUP BY than normal SQL. All columns in the select-list that aren’t part of an aggregate expression must be included in the GROUP BY in most SQL implementations.
Answered by Bill Karwin
Post is based on https://stackoverflow.com/questions/176964/select-top-10-records-for-each-category