I have a table that generally looks like:

Name varchar       | Location varchar[]
Lemoney    | {Home,Work,Home,Stack Exchange,,,,,,}
Other Dude | {Home,,,,,,,,,}
Busy Dude  | {Home,Work,Work,Work,Home,Work,,,,}

I have been trying to write a query that will return the Last Value for each Name like:

Lemony     | Stack Exchange
Other Dude | Home
Busy Dude  | Work

I haven't been able to figure out how to return that yet. Does anyone have any ideas?

  • Is location an array column, e.g. text[]?
    – user1822
    Commented Apr 13, 2016 at 13:53
  • VARCHAR[] my apologies.. fixing question
    – lemoney
    Commented Apr 13, 2016 at 13:55

1 Answer 1

SELECT distinct Name, loc 
  SELECT  *, last_value(Location) over (partition by Name) as loc 
    FROM (SELECT Name, unnest(Location) as Location FROM table)  x) y;

Should answer your question

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.