CODEWITHSUNDEEP
mysqldatabasepostgresqlselect
budadabubladend
budadabubladend

Reputation: 23

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.

Upvotes: 2

Views: 1288

Answers (3)

klin
klin

Reputation: 121534

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)

Upvotes: 1

tata.leona
tata.leona

Reputation: 1058

Here is my solution

select t1.date as date, (
    select  date
        from mytable where state = 1 and t1.date::date - date::date >= 0 order by t1.date::date - date::date  limit 1
    ) as last_date
,t1.state , t1.id from mytable t1 where state = 2

Upvotes: 0

LuckAss
LuckAss

Reputation: 114

I would suggest something like this (I'm not sure everything in the syntax is correct):

CREATE OR REPLACE FUNCTION dates() RETURNS TABLE (date DATE, lastdate DATE,
state INTEGER, id INTEGER) AS $$
DECLARE
   lastType1 DATE;
BEGIN
   FOR d IN (SELECT * FROM tableName)
   LOOP
       IF d.state == 1 THEN
            lastType1 = d.date;
       END IF;
       RETURN NEXT (d.date, lastType1, d.state, d.id);
   END LOOP;
   RETURN;
END
LANGUAGE 'plpgsql';

This you can call by SELECT * FROM dates(), if you want to access it like a table i would recommend creating a view calling the function

You just have to put instead of dates() what you want to call your function and instead of tableNamewhat you table is actually called

Upvotes: 0

Related Questions