Select top 10 records for each category

You can try this approach.
This query returns 10 most populated cities for each country.

SELECT city, country, population
FROM
(SELECT city, country, population,
@country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM cities
ORDER BY country, population DESC
) ranked
WHERE country_rank <= 10;

Tried the following and it worked with ties too.

SELECT rs.Field1,rs.Field2
FROM (
SELECT Field1,Field2, ROW_NUMBER()
OVER (Partition BY Section
ORDER BY RankCriteria DESC ) AS Rank
FROM table
) rs WHERE Rank <= 10

While the question was about SQL Server 2005, most people have moved on and if they do find this question, what could be the preferred answer in other situations is one [using `CROSS APPLY` as illustrated in this blog post][1].

<!-- language: lang-sql -->

SELECT *
FROM t
CROSS APPLY (
SELECT TOP 10 u.*
FROM u
WHERE u.t_id = t.t_id
ORDER BY u.something DESC
) u

This query involves 2 tables. The OP's query only involves 1 table, in case of which a window function based solution might be more efficient.

[1]:

[To see links please register here]

If we use SQL Server >= 2005, then we can solve the task with one *select* only:


declare @t table (
Id int ,
Section int,
Moment date
);

insert into @t values
( 1 , 1 , '2014-01-01'),
( 2 , 1 , '2014-01-02'),
( 3 , 1 , '2014-01-03'),
( 4 , 1 , '2014-01-04'),
( 5 , 1 , '2014-01-05'),

( 6 , 2 , '2014-02-06'),
( 7 , 2 , '2014-02-07'),
( 8 , 2 , '2014-02-08'),
( 9 , 2 , '2014-02-09'),
( 10 , 2 , '2014-02-10'),

( 11 , 3 , '2014-03-11'),
( 12 , 3 , '2014-03-12'),
( 13 , 3 , '2014-03-13'),
( 14 , 3 , '2014-03-14'),
( 15 , 3 , '2014-03-15');


-- TWO earliest records in each Section

select top 1 with ties
Id, Section, Moment
from
@t
order by
case
when row_number() over(partition by Section order by Moment) <= 2
then 0
else 1
end;


-- THREE earliest records in each Section

select top 1 with ties
Id, Section, Moment
from
@t
order by
case
when row_number() over(partition by Section order by Moment) <= 3
then 0
else 1
end;


-- three LATEST records in each Section

select top 1 with ties
Id, Section, Moment
from
@t
order by
case
when row_number() over(partition by Section order by Moment desc) <= 3
then 0
else 1
end;

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

*Note: I know the OP has only 3 groups but this is a known general problem for many developers and there's no really good solution in SQL. So let me show you another way.*

----------


### IN THEORY:

You can write it as one query. That is, *formally one query,* but it contans either subqueries or a self-JOIN which makes it in fact multiple operations under the hood. So you might as well just select each group individually.

----------


### IN PRACTICE:

If you want a performant solution, you need to work a bit more. Let's say you have 100 employees, you have 26 buildings from A to Z, people move around them (enter/leave), and you need the last 5 events for every building.

```text
EVENT_ID EVENT_TIME EMPOYEE_ID EVENT_CODE BUILDING
-------------------------------------------------------------------------
883691 2023-03-29 11:00:00 92 enter A
883690 2023-03-29 11:00:21 78 enter C
883689 2023-03-29 11:00:25 58 enter A
883688 2023-03-29 11:02:10 22 leave H
883687 2023-03-29 11:31:42 73 leave P
...
...
```


You want to avoid 26 queries.

Here's what you can do:

1. Write a query with a simple ORDER BY EVENT_ID DESC (or EVENT_TIME DESC), to get the last N events for all buildings.
2. Set N (the limit) to a reasonable estimate that will have data for most buildings, you don't need to have *all of them* but the more the better. Let's say LIMIT 5000.
3. Process the result set on the application side, to see which buildings don't have the top 10 in it.
4. For those buildings, run separate queries to get their top 10.

For theorists, this is an anti-pattern. But the first query will have almost the same performance as one of the single-building ones, and probably brings you most of what you need; a few buildings will be missing, depending on how the employees normally move. Then, you may need 5 more queries for those buildings, and some merging on the application level.

**In short: get a result that's almost complete, then make it complete.**

So if you need performance, this is one way.
If you need clarity of the business logic, well, choose any other answer. This one is scary. But when you go for speed, you often need scary techniques.