Oracle DB dynamic returned value based on ID-CodePudding

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.

CodePudding user response：

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>

CodePudding user response：

You can use an analytic function such as ROW_NUMBER()(by which you'll strictly pick two per each match, even a tie occurs) as follows

SELECT Material, qty, month_year
  FROM(
       SELECT ROW_NUMBER() OVER (PARTITION BY Material ORDER BY month_year DESC) AS rn,
              t.*
         FROM t
      ) 
 WHERE rn <= 2

after sorting descendingly by month_year column's values based on the desired output.