Oracle DB dynamic returned value based on ID

Question

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.

The first two rows per material? But 2020-07 and 2020-04 are not the first rows for material 1. 2020-03 and 2020-04 are the earliest. Or what is your definition of "first"? — Thorsten Kettner, Feb 02 '23 at 07:43
Ah, just two rows per material, and you don't care which? Just the first two the DBMS happens to pick? — Thorsten Kettner, Feb 02 '23 at 07:51
"the first two rows that return" In SQL, there is no first row, third row, fifth row or anything else, there is no order at all unless you use an ORDER BY clause. So your question at it currently stands doesn't make sense. — Jonas Metzler, Feb 02 '23 at 07:51

score 1 · Accepted Answer · answered Feb 02 '23 at 07:47

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>

Barbaros Özhan · Answer 2 · 2023-02-02T07:53:14.370

0

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.

edited Feb 02 '23 at 07:53

answered Feb 02 '23 at 07:43

Barbaros Özhan

59,113
10
31
55

Oracle DB dynamic returned value based on ID

2 Answers2