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.
