0

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
Matthew Farant
  • 82
  • 1
  • 9
  • What about someone with Thailand, Japan, Thailand, Japan, Canady? –  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

1 Answers1

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%'
eshirvana
  • 23,227
  • 3
  • 22
  • 38