Search code examples
sqlarrayspostgresqlsql-order-bygreatest-n-per-group

Get first matched row for each element in SQL ANY array

I have the following schema:

CREATE TABLE test (
    key character(128),
    value character(128),
    timestamp int
);

With three rows:

  • key1 -> value1 -> 100
  • key2 -> value5 -> 200
  • key1 -> value15 -> 300

I would like to run a query, where I could get key value pairs for given key. So, for example running

SELECT key, value
FROM test
WHERE key = ANY(array['key1', 'key2'])
ORDER by timestamp DESC
LIMIT 1

would return

key1, value15
key2, value5

Right now, the query above returns the most recent element only. How should I modify it to return the most recent element for each item from the 'any' array? Thanks!

Solution

  • I think the simplest approach is to unnest the array, and use distinct on:

    select distinct on (a.key) a.key, t.value
from test t
inner join unnest(array['key1', 'key2']) a(key) on a.key = t.key
order by a.key, t.timestamp desc