SELECT TOP 2 PER GROUP

Database
Fan

SELECT TOP 2 PER GROUP

I am trying to pull out a set number of rows (2) per Employee from a table that shows a sample of all the work they did. It is for quality control purposes. There are a variety of different jobs they perform and for every different job type they perform I want 2 examples of it.

The basic query is something like

SELECT Name, JobType, JobID
FROM JOBS
WHERE
JobEndDate between StartDate and EndDate

Name JobType JobID
David OpenAcct 123
David OpenAcct 456
David OpenAcct 789
David OpenAcct 888
Sarah CloseAcct 111
Sarah OpenAcct 222
Sarah CloseAcct 666
Emily OutboundCall 999
Emily InboundCall 333
Emily InboundCall 777
Emily InboundCall 001
etc.

So I just want to see two examples of David with JobType OpenAcct, 2 examples of Sarah with CloseAcct, 2 examples of Emily with InboundCall etc.
Also if they only did 1 of that particular JobType I would want to see that too.
3 REPLIES
Teradata Employee

Re: SELECT TOP 2 PER GROUP

Try this:

select
name
,jobtype
,jobid
from
(
select
name
,jobtype
,jobid
,ROW_NUMBER() OVER (PARTITION BY NAME, jobtype ORDER BY jobid) as tmp_nbr
from table_name
) dt
where tmp_nbr < 3
order by 1,2,3
;
Senior Apprentice

Re: SELECT TOP 2 PER GROUP

The answer to your problem is an OLAP function:

SELECT Name, JobType, JobID
FROM JOBS
WHERE JobEndDate between StartDate and EndDate
qualify
row_number() over (partition by Name, JobType order by JobType) <= 2

Dieter
Fan

Re: SELECT TOP 2 PER GROUP

Thank you both! Works perfectly!