First n records for every group

[bdiaz]

I need a SQL Statement to return the first n records of every group (for example, the first 10 customers of every department), how can this be done.

 

[bdiaz]

The SQL statement can be like this

SELECT dpto,cli from TBL TA Where TA.cli IN
(select top 2 cli from TBL TB
where TA.dpto=TB.dpto Order by cli)

 

[maswien]

Here is the example table I created for demo:

table: sales

customers            dept                 
-------------------- ------
mark                 IT
Jones                IT
peter                IT
lincon               IT
dell                 IT
will                 HR
al                   HR
davi                 HR
lincon               HR
susan                HR

following query shoud get the first 3 records for each department, ordered by customer's name
[ocde]
select t2.customers, t2.dept
from
sales t2 inner join
(
select t1.customers, t1.dept
from
sales t0 inner join sales t1
on t0.dept = t1.dept and t0.customers < t1.customers
group by t1.customers, t1.dept
having count(*) = 3
) t3
on t2.dept = t3.dept and t2.customers < t3.customers
order by t2.dept, t2.customers
[/code]

following are the results of this query:

customers            dept                 
-------------------- -------------------- 
al                   HR
davi                 HR
lincon               HR
dell                 IT
Jones                IT
lincon               IT