How to take n rows of data from a table referring data from a column in the same table? (SQL)-CodePudding

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

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

CodePudding user response：

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;