how to optimize multiple unions for the same table with different condition into a single query

Question

Environment : PostgreSQL v10.6 (AWS RDS instance) For the given table:

CREATE temp TABLE lve
    ("fin" timestamptz, "vehiculo" int, "equipo" int)
;
INSERT INTO lve
    ("fin", "vehiculo", "equipo")
VALUES
    ('2021-12-01 00:00:00', 1, 888),
    (NULL, 3, 888),
    ('2021-05-01 00:00:00', 2, 888),
    ('2021-11-05 00:00:00', 10, 333),
    (NULL, 9, 333),
    ('2021-09-05 00:00:00', 5, 333)
;

I need the last record (by 'fin' column) where 'fin' is not null, for every value in a given list of 'equipo'.

For a single, given 'equipo', this query works:

select * from lve
where equipo = 333 and fin is not null
order by fin desc
limit 1

for two 'equipo' , with a UNION I can get my desired result:

(select * from lve
where equipo = 333 and fin is not null
order by fin desc
limit 1)
union 
(select * from lve
where equipo = 888 and fin is not null
order by fin desc
limit 1);

result:

fin	vehiculo	equipo
2021-11-05 00:00:00.000 -0300	10	333
2021-12-01 00:00:00.000 -0300	1	888

But since in reality the table contains much more data for many others vehiculo/equipo and I can have a list with N 'equipo' to query for, it's not optimal to continue adding multiple UNIONs manually.

Is there a way that I can rewrite this into a single query where i can just pass the list/array of given 'equipo' (888, 333, nnn) ?

PD: My plan B is to create a function that returns my desired record for a single 'equipo' and then use that with an array, but I really want to know if this can be done in the way I'm asking.

Thanks.

score 0 · Accepted Answer · answered Jan 26 '22 at 16:37

You can use row_number() to order by fin for each equipo and then choose the first/last from there:

select fin, vehiculo, equipo
from (
  select fin, vehiculo, equipo
     , row_number() over (partition by equipo order by fin desc) as rn
  from  lve
  where fin is not null
) as t
where rn = 1;
fin vehiculo    equipo
2021-11-05 00:00:00+00  10  333
2021-12-01 00:00:00+00  1   888

See Fiddle

how to optimize multiple unions for the same table with different condition into a single query

1 Answers1