I have one table that have three fields
Material QTY MONTH_YEAR
1 , 1 2020-07
1 , 2 2020-04
1 , 2 2020-03
1 , 2 2020-05
2 , 3 2020-10
2 , 4 ,2020-08
2 , 4 ,2020-08
3 , 4 ,2021-08
3 , 4 ,2021-08
3 , 4 ,2020-08
3 , 4 ,2020-08
3 , 4 ,2019-08
What i am try to achieve is to return first two records for different material number.
Output
Material QTY MONTH_YEAR
1 , 1 2020-07
1 , 2 2020-04
2 , 3 2020-10
2 , 4 ,2020-08
3 , 4 ,2021-08
3 , 4 ,2021-0
Thanks in advance.
>Solution :
Sample data:
SQL> select * from test order by material, month_year;
MATERIAL QTY MONTH_Y
---------- ---------- -------
1 2 2020-03
1 2 2020-04
1 2 2020-05
1 1 2020-07
2 4 2020-08
2 4 2020-08
2 3 2020-10
3 4 2019-08
3 4 2020-08
3 4 2020-08
3 4 2021-08
3 4 2021-08
12 rows selected.
Generally speaking, one option is to sort rows by something – in my example, it is per each material by month_year column in ascending order so that the 1st and the 2nd row have these rn values: 1 and 2 – and then fetch rows that rank as two highest.
Your desired output shows that you actually want two rows as you literally wrote them, but – that’s suspicious because for material = 1 are either 2020-07 and 2020-05 (if sorted in descending order), or 2020-03 and 2020-04 (if sorted in ascending order). If there’s no other column that lets us figure out such an output (the one you specified), you’re out of luck.
On the other hand, if your desired output is wrong, then fix order by clause (in line #3) to let query return values you need (either 2 first, or 2 last rows).
SQL> with temp as
2 (select material, qty, month_year,
3 row_number() over (partition by material order by month_year) rn
4 from test
5 )
6 select material, qty, month_year
7 from temp
8 where rn <= 2
9 order by material, month_year;
MATERIAL QTY MONTH_Y
---------- ---------- -------
1 2 2020-03
1 2 2020-04
2 4 2020-08
2 4 2020-08
3 4 2019-08
3 4 2020-08
6 rows selected.
SQL>