CODEWITHSUNDEEP
sqloracle-database
Spectre226
Spectre226

Reputation: 33

Oracle SQL : Getting one result set for multiple select queries on the same table

This is my first post on stackoverflow so please be patient. Assume I have an employee table with roles a,b,c,d, etc. I needed the result of the first 5 employees with role A, the first 5 employees with role B and the first 5 employees with role C. Please note that I need all columns of the table in my result set. Any help will be much appreciated.

SAMPLE DATA

Name---Dept---Role  
abc1---HR---A  
abc2---HR---B  
abc3---HR---C  
abc4---HR---D  
abc5---HR---A  
abc6---HR---B  
abc7---HR---C  
abc8---DEV---D  
abc9---DEV---A  
abc10---DEV---B  
abc11---DEV---C  
abc12---DEV---D  
abc13---DEV---A  
abc14---DEV---B  
abc15---DEV---C  
abc16---Test---D  
abc17---Test---A  
abc18---Test---B  
abc19---Test---C  
abc20---Test---D  
abc21---Test---A  
abc22---Test---B  
abc23---Test---C  
abc24---Test---D  
abc25---Test---A  
abc26---Test---B

REQUIRED OUTPUT

Name---Dept---Role  
abc1---HR---A  
abc5---HR---A  
abc9---DEV---A  
abc13---DEV---A  
abc17---Test---A  
abc2---HR---B  
abc6---HR---B  
abc10---DEV---B  
abc14---DEV---B  
abc18---Test---B  
abc3---HR---C  
abc7---HR---C  
abc11---DEV---C  
abc15---DEV---C  
abc19---Test---C

Thanks, Sud

Upvotes: 0

Views: 50

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to rank records (first/best five per role). Such is done with analytic functions such as RANK, DENSE_RANK and ROW_NUMBER.

select name, dept, role
from
(
  select name, dept, role, row_number() over (partition by role order by name) as rn
  from employees
  where role in ('A', 'B', 'C')
)
where rn <= 5
order by role, name;

Upvotes: 1

Related Questions