CODEWITHSUNDEEP
sqlpostgresql
nenad007
nenad007

Reputation: 139

SQL (PostgreSQL): Select Rows equal value together with next two records matching same key

Is there a way to get from a table every record where the column body = "Hey" together with the next two records who have same "dest"? But, everytime the same dest got in body = "Hey" I need this record with the next two records matchin same dest again.

A partition don't help me as I can not "reset" it for the next pair of results who have same dest and body = "Hey".

id | dest | body | received
1  | A    | Hey  | 2016-10-28 01:00:00
5  | B    | Hey  | 2016-10-28 02:00:00
6  | B    | X11  | 2016-10-28 03:00:00
8  | A    | Y11  | 2016-10-28 04:00:00
11 | A    | Y12  | 2016-10-28 05:00:00
20 | C    | Hey  | 2016-10-28 06:00:00
22 | A    | Y13  | 2016-10-28 07:00:00
25 | B    | X12  | 2016-10-28 08:00:00
26 | A    | Hey  | 2016-10-28 09:00:00  ! same "dest", if body = "Hey" we need it
29 | A    | Y22  | 2016-10-28 10:00:00
33 | B    | X13  | 2016-10-28 11:00:00
35 | A    | Y33  | 2016-10-28 12:00:00

Result:

1  | A    | Hey  | 2016-10-28 01:00:00
8  | A    | Y11  | 2016-10-28 04:00:00
11 | A    | Y12  | 2016-10-28 05:00:00
5  | B    | Hey  | 2016-10-28 02:00:00
6  | B    | X11  | 2016-10-28 03:00:00
20 | B    | X12  | 2016-10-28 08:00:00
20 | C    | Hey  | 2016-10-28 06:00:00
26 | A    | Hey  | 2016-10-28 09:00:00
29 | A    | Y22  | 2016-10-28 10:00:00
35 | A    | Y33  | 2016-10-28 12:00:00
...

Upvotes: 0

Views: 48

Answers (1)

klin
klin

Reputation: 121634

select id, dest, body, received
from (
    select 
        *, 
        row_number() over (partition by dest, part) rn
    from (
        select 
            *,
            sum((body = 'Hey')::int) over (partition by dest order by id) part
        from a_table
        ) s
    ) s
where rn < 4
order by dest, id;

 id | dest | body |      received       
----+------+------+---------------------
  1 | A    | Hey  | 2016-10-28 01:00:00
  8 | A    | Y11  | 2016-10-28 04:00:00
 11 | A    | Y12  | 2016-10-28 05:00:00
 26 | A    | Hey  | 2016-10-28 09:00:00
 29 | A    | Y22  | 2016-10-28 10:00:00
 35 | A    | Y33  | 2016-10-28 12:00:00
  5 | B    | Hey  | 2016-10-28 02:00:00
  6 | B    | X11  | 2016-10-28 03:00:00
 25 | B    | X12  | 2016-10-28 08:00:00
 20 | C    | Hey  | 2016-10-28 06:00:00
(10 rows)

Upvotes: 1

Related Questions