Wider dataset in posgresql - SQL

Question

I have the following table:

CREATE TABLE my_table 
(
    the_debt_id varchar(6) NOT NULL, 
    the_debt_paid date NOT NULL, 
    the_debt_due date NOT NULL
)

INSERT INTO my_table
VALUES ('LMUS01', '2019-05-03', '2019-05-02'), 
       ('LMUS01', '2019-06-03', '2019-06-02'), 
       ('LMUS01', '2019-07-01', '2019-07-02'), 
       ('LMUS02', '2019-05-03', '2019-05-07'), 
       ('LMUS02', '2019-06-07', '2019-06-07')

And I extracted the last and next-to-last record by the_debt_paid

select * from
(
SELECT *, row_number()
OVER (PARTITION BY the_debt_id ORDER BY the_debt_paid DESC) as rn
FROM my_table
)A where rn<=2

So I have this result:

the_debt_id    the_debt_paid     the_debt_due
LMUS01         2019-07-01        2019-07-02
LMUS01         2019-06-03        2019-06-02
LMUS02         2019-06-07        2019-06-07
LMUS02         2019-05-03        2019-05-07

Is there a way to get a wider form in my SQL sentence to get this expected output, by branching the_debt_paid?

the_debt_id    the_debt_paid_last     the_debt_paid_next
LMUS01         2019-07-01             2019-06-03
LMUS02         2019-06-07             2019-05-03

I'm stuck with this code:

select CASE 
WHEN rn = 1 THEN the_debt_paid_last = the_debt_paid 
WHEN rn = 2 THEN the_debt_paid_next = the_debt_paid
END
FROM (
select * FROM
(
SELECT *, row_number()
OVER (PARTITION BY the_debt_id ORDER BY the_debt_paid DESC) as rn
FROM my_table) A 
WHERE rn<=2) final

score 3 · Accepted Answer · answered Sep 01 '20 at 16:00

3

You can do conditional aggregation:

select 
    the_debt_id,
    max(the_debt_paid) filter(where rn = 1) the_debt_paid_last,
    max(the_debt_paid) filter(where rn = 2) the_debt_paid_next
from (
    select 
        t.*, 
        row_number() over (partition by the_debt_id order by the_debt_paid desc) as rn
    from my_table t
) a 
where rn <= 2
group by the_debt_id

answered Sep 01 '20 at 16:00

GMB

216,147
25
84
135

Hello @GMB, the answer works fine, just a question, is the use of function `MAX` a way to isolate the column `the_debt_paid`? – Manu Sep 01 '20 at 16:09
1

By the way, this is the fiddle for the question and answer [https://dbfiddle.uk/?rdbms=postgres_12&fiddle=da1ad429cf9279396b902b59ae47ae52] – Manu Sep 01 '20 at 16:10
1

@Manu: yes, exactly. – GMB Sep 01 '20 at 16:22
2

So I guess it could have been the function `MIN` because only one row meets the condition! Thank you! – Manu Sep 01 '20 at 16:24

Wider dataset in posgresql - SQL

1 Answers1