budadabubladend budadabubladend - 3 months ago 14
MySQL Question

How to get closest date for specific rows in postgresql

I have the following problem:

I have a table containing two types of data. One with state = 1 and one with state = 2. For every row with state = 2 I need the last corresponding date with state = 1. Here's an example:

date state id
'2016-04-03' 1 1
'2016-04-04' 1 1
'2016-04-05' 2 1
'2016-04-06' 2 1


Expected result:

date lastdate state id
'2016-04-03' '2016-04-03' 1 1
'2016-04-04' '2016-04-04' 1 1
'2016-04-05' '2016-04-04' 2 1
'2016-04-06' '2016-04-04' 2 1
'2016-04-07' '2016-04-07' 1 1


I don't exactly know where I am stuck. It feels like this problems you're looking at the wrong way so any help is appreciated.

Thx in advance!

Edit

As it seems like I didn't explain my problem good enough, here's another try:

For every row I need the date of the last row with the same id which had state = 1. If the state is already 1 the date of the same row is needed.

So to clarify my "expected results" - Table:

date lastdate state id
'2016-04-03' '2016-04-03' 1 1


state is 1 so date of row is last date with state 1

'2016-04-04' '2016-04-04' 1 1


state is 1 so date of row is last date with state 1

'2016-04-05' '2016-04-04' 2 1


state is 2 so date of the last row with date 1 is from the row before

'2016-04-06' '2016-04-04' 2 1


state is 2, last date with state = 1 is '2016-04-04'

'2016-04-07' '2016-04-07' 1 1


state is 1 again, so same date

'2016-04-08' '2016-04-07' 2 1


state is 2, so date of last row (row before) is set

I hope my intentions are understandable now.

Answer

This can be accomplished by joining a table with itself:

select distinct on (a.date) 
    a.date date, 
    coalesce(b.date, a.date) last_date, 
    a.state, 
    a.id
from test a
left join test b 
on a.state = 2 and b.state = 1 and b.date < a.date
order by a.date, b.date desc;

    date    | last_date  | state | id 
------------+------------+-------+----
 2016-04-03 | 2016-04-03 |     1 |  1
 2016-04-04 | 2016-04-04 |     1 |  1
 2016-04-05 | 2016-04-04 |     2 |  1
 2016-04-06 | 2016-04-04 |     2 |  1
 2016-04-07 | 2016-04-07 |     1 |  1
(5 rows)