CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
bereal
bereal

Reputation: 34272

Postgres: getting latest rows for an array of keys

I have a simple table for the events log:

uid | event_id | event_data
----+----------+------------
  1 |  1       | whatever
  2 |  2       |
  1 |  3       |
  4 |  4       |
  4    5       |

If I need the latest event for a given user, that's obvious:

SELECT * FROM events WHERE uid=needed_uid ORDER BY event_id DESC LIMIT 1

However, suppose I need the latest events for each user id in an array. For example, for the table above and users {1, 4} I'd expect events {3, 5}. Is that possible in plain SQL without resorting to a pgSQL loop?

Upvotes: 2

Views: 603

Answers (5)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

To return rows in the original order of array elements:

Postgres 9.4 or newer

SELECT e.*
FROM   unnest('{1, 4}'::int[]) WITH ORDINALITY a(uid, ord)  -- input array here
CROSS  JOIN LATERAL (
   SELECT * FROM events e
   WHERE  e.uid = a.uid
   ORDER  BY e.event_id DESC
   LIMIT  1
   ) e
ORDER  BY a.ord;

Details for WITH ORDINALITY:

There is a subtle difference to the @a_horse's query: If the given array has duplicate elements, this query gets duplicate rows in return, which may or may not be desirable. If it's not, add a DISTINCT step after unnest() and before the join to the big table.

The main benefit is optimized index usage. See:

Postgres 9.3 or older

Using an implicit JOIN LATERAL:

SELECT e.*
FROM  (SELECT '{1, 4}'::int[]) a(arr)  -- input array here
     , generate_subscripts(a.arr, 1) i 
CROSS  JOIN LATERAL (
   SELECT * FROM event e
   WHERE  e.uid = a.arr[i.i]
   ORDER  BY e.event_id DESC
   LIMIT  1
   ) e
ORDER  BY i.i;

Upvotes: 1

user330315
user330315

Reputation:

A Postgres specific solution is to use distinct on which is usually faster than the solution using a window function:

select distinct on (uid) uid, event_id, event_data
from events 
where uid in (1,4)
order by uid, event_id DESC

Upvotes: 3

bereal
bereal

Reputation: 34272

This came to me few seconds after I posted the question. It's not as efficient, but to consider all the options:

SELECT * FROM events WHERE event_id IN 
    (SELECT MAX(event_id) FROM events GROUP BY uid WHERE uid IN (1,4))

Upvotes: 0

DirkNM
DirkNM

Reputation: 2664

Maybe this helps:

SELECT uid,
       event_id
  FROM(SELECT uid,
              event_id,
              ROW_NUMBER() OVER (PARTITION BY uid ORDER BY event_ID DESC) rank
         FROM events
      )
 WHERE uid IN (1, 4)
   AND rank = 1

Upvotes: 1

Deep
Deep

Reputation: 3202

try below query :

select DesiredColumnList 
from 
(
    select *, row_number() over ( partition by uid order by event_id desc) rn
    from yourtable
) t
where rn = 1

Row_Number will assign unique number starting from 1 to each row order by event_id desc and partition by will ensure that numbering should be done for each group of uid.

Upvotes: 2

Related Questions