I have a table with many data and a sample is as follows
| product_table | data | data2 | data3 | data4 | data5 | data6 | data7 | |
|---|---|---|---|---|---|---|---|---|
| product id | 001 | 002 | 003 | 004 | 005 | 006 | 007 | |
| product name | a | b | c | d | e | f | g | |
| status | available | not available | damaged | available | available | not available | damaged | |
| order id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 9 |
I need to extract 2 order ids from each of the statuses from one query.
The out put I want is as follows
| status | order id |
|---|---|
| available | 1 |
| available | 4 |
| not available | 2 |
| not available | 6 |
| damaged | 3 |
| damaged | 7 |
I tried using "Limit" but it only brings out the first few data which is in the table.
Query I used
Select order id , status
from table product_table
limit 2
;
output I got was
| status | order id |
|---|---|
| available | 1 |
| not available | 2 |
>Solution :
You can partition the database by status and use row_number to extract two records.
select status, order_id from (
select status, order_id, row_number() over (partition by status) as rn from product_table)
where rn <=2;