How to get rows from same table with different conditions

user_6782349 0
Viewed 140

I want to get data from same table with different conditions. I am using sqlite in android studio. 

**ID    Name       Role**
1   Emma       Manager
2   Olivia     Manager
3   Ava        Manager
4   Isabella   Sales officer
5   Sophia     Sales Officer
6   Charlotte  Sales Officer
7   Mia        Clerk
8   Amelia     Clerk

Assume this table, I have different types of roles and different numbers of persons in a role. I want to select 2 persons from each role.

Select * from employeeTable where role = 'Manager' LIMIT 2
Select * from employeeTable where role = 'Sales officer' LIMIT 2
Select * from employeeTable where role = 'Clerk' LIMIT 2

Simply I want to join the result of these three queries. Sorry if the childish question. And thanks in advance

sqlite android
2 Answers

With row_number() window function:

select t.id, t.name, t.role 
from (
  select *, row_number() over (partition by role) rn
  from employeeTable
) t
where t.rn <= 2

You can also define:

partition by role order by name

or:

partition by role order by id

instead of just:

partition by role

to get specific rows in the results.
See the demo.
Results:

| ID  | Name     | Role          |
| --- | -------- | ------------- |
| 7   | Mia      | Clerk         |
| 8   | Amelia   | Clerk         |
| 1   | Emma     | Manager       |
| 2   | Olivia   | Manager       |
| 4   | Isabella | Sales Officer |
| 5   | Sophia   | Sales Officer |
user_104988280

Try is query and check if working:

Select * from employeeTable where role = 'Manager' LIMIT 2
UNION ALL
Select * from employeeTable where role = 'Sales officer' LIMIT 2
UNION ALL
Select * from employeeTable where role = 'Clerk' LIMIT 2
user_129667980
Related