List down values that match a specific sequence in other column in PostgreSQL

Question

Suppose we have a table that shows the vacation destinations for some people with their vacation date (in January).

Date       |   Name   |   Destination
01/01/2021 |   Andy   |   Thailand
01/01/2021 |   Ben    |   Singapore
02/01/2021 |   Andy   |   Japan
02/01/2021 |   Andy   |   Canada
03/01/2021 |   Ben    |   USA
04/01/2021 |  James   |   Thailand
05/01/2021 |  James   |   Thailand
06/01/2021 |  James   |   Japan
07/01/2021 |  Ben     |   Canada
08/01/2021 |  James   |   Canada

How do we list down those who have destinations in the exact following order: Thailand - Japan - Canada? (If someone has destination in the same country multiple times in sequential order then it counts as one appearance)

The expected output is the name of the person:

Name      
Andy    
James

What about someone with Thailand, Japan, Thailand, Japan, Canady? — a_horse_with_no_name, Apr 28 '21 at 14:01
If there's a "Thailand-Japan-Canada" in the route, then it's accepted. Another Example: Germany-Thailand-Japan-Canada-Austria is accepted — Matthew Farant, Apr 28 '21 at 14:03

score 1 · Accepted Answer · answered Apr 28 '21 at 14:06

1

you can use string_agg:

select Name , string_agg(Destination, '-' order by date) journey
from table
group by name 
having journey ilike '%Thailand-Japan-Canada%'

answered Apr 28 '21 at 14:06

eshirvana

20,424
3
21
36

List down values that match a specific sequence in other column in PostgreSQL

1 Answers1