Get only one record in a group by expression

November 9, 2021

Let’s say I have a salary table with a ‘paymonth’ column (date of payment) ‘code1’ column (some human resources code) and ‘code2’ column (another human resources code).

Here is the content of the table:

paymonth        code1      code2    payment
-------------------------------------------
01/01/2021      11             3    1000
01/01/2021      11             1    750
01/02/2021      11             3    650

For every different couple of code1, code2, I would like to get the last rows entered. The problem here is that line 1 and line 2 have the same paymonth date.

So a query like this one:

select * from salary
order by paymonth
fetch first 2 row only;

would give:

01/01/2021      11             3    1000
01/02/2021      11             3    650

This is not what I want. I would like this (different codes):

01/01/2021      11             1    750
01/02/2021      11             3    650

Can you help?

>Solution :

I’d think of analytic functions. I used row_number, maybe rank could/should be used (depending on data).

SQL> with salary (paymonth, code1, code2, payment) as
  2    (select date '2021-01-01', 11, 3, 1000 from dual union all
  3     select date '2021-01-01', 11, 1,  750 from dual union all
  4     select date '2021-01-02', 11, 3,  650 from dual
  5    ),
  6  temp as
  7    (select s.*,
  8            row_number() over (partition by code1, code2 order by paymonth desc) rn
  9     from salary s
 10    )
 11  select * from temp
 12  where rn = 1;

PAYMONTH        CODE1      CODE2    PAYMENT         RN
---------- ---------- ---------- ---------- ----------
01/01/2021         11          1        750          1
01/02/2021         11          3        650          1

SQL>