Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


CREATE TABLE table_first (
   a VARCHAR(20),
   created_at timestamp,
   b INTEGER,
   c INTEGER);
INSERT INTO table_first VALUES
  ('LK0601','2022-03-01 10:00:00',1,1),
  ('LK0601','2022-03-02 10:00:00',1,2),
  ('LK0601','2022-03-03 10:00:00',1,3),

  ('DT9834','2022-03-01 10:00:00',2,1),
  ('DT9834','2022-03-02 10:00:00',2,2),
  ('DT9834','2022-03-03 10:00:00',2,3),

  ('LM3526','2022-03-01 10:00:00',3,1),
  ('LM3526','2022-03-02 10:00:00',3,2),
  ('LM3526','2022-03-03 10:00:00',3,3)
  ;
9 rows affected

(SELECT a, created_at time, b, c 
FROM table_first 
WHERE created_at >= timestamp '2022-02-13 00:00:00' AND a = 'LK0601' 
ORDER BY time desc limit 1)

UNION

(SELECT a, created_at time, b, c 
FROM table_first 
WHERE created_at >= timestamp '2022-02-13 00:00:00' AND a = 'DT9834' 
ORDER BY time desc limit 1)

UNION

(SELECT a, created_at time, b, c 
FROM table_first 
WHERE created_at >= timestamp '2022-02-13 00:00:00' AND a = 'LM3526' 
ORDER BY time desc limit 1)

ORDER BY a;


a
time
b
c


DT9834
2022-03-03 10:00:00
2
3

LK0601
2022-03-03 10:00:00
1
3

LM3526
2022-03-03 10:00:00
3
3


explain 
(SELECT a, created_at time, b, c 
FROM table_first 
WHERE created_at >= timestamp '2022-02-13 00:00:00' AND a = 'LK0601' 
ORDER BY time desc limit 1)

UNION

(SELECT a, created_at time, b, c 
FROM table_first 
WHERE created_at >= timestamp '2022-02-13 00:00:00' AND a = 'DT9834' 
ORDER BY time desc limit 1)

UNION

(SELECT a, created_at time, b, c 
FROM table_first 
WHERE created_at >= timestamp '2022-02-13 00:00:00' AND a = 'LM3526' 
ORDER BY time desc limit 1)

ORDER BY a;


QUERY PLAN


Sort  (cost=66.17..66.18 rows=3 width=74)

  Sort Key: table_first.a

  ->  HashAggregate  (cost=66.12..66.15 rows=3 width=74)

        Group Key: table_first.a, table_first.created_at, table_first.b, table_first.c

        ->  Append  (cost=22.01..66.09 rows=3 width=74)

              ->  Limit  (cost=22.01..22.02 rows=1 width=74)

                    ->  Sort  (cost=22.01..22.02 rows=1 width=74)

                          Sort Key: table_first.created_at DESC

                          ->  Seq Scan on table_first  (cost=0.00..22.00 rows=1 width=74)

                                Filter: ((created_at >= '2022-02-13 00:00:00'::timestamp without time zone) AND ((a)::text = 'LK0601'::text))

              ->  Limit  (cost=22.01..22.02 rows=1 width=74)

                    ->  Sort  (cost=22.01..22.02 rows=1 width=74)

                          Sort Key: table_first_1.created_at DESC

                          ->  Seq Scan on table_first table_first_1  (cost=0.00..22.00 rows=1 width=74)

                                Filter: ((created_at >= '2022-02-13 00:00:00'::timestamp without time zone) AND ((a)::text = 'DT9834'::text))

              ->  Limit  (cost=22.01..22.02 rows=1 width=74)

                    ->  Sort  (cost=22.01..22.02 rows=1 width=74)

                          Sort Key: table_first_2.created_at DESC

                          ->  Seq Scan on table_first table_first_2  (cost=0.00..22.00 rows=1 width=74)

                                Filter: ((created_at >= '2022-02-13 00:00:00'::timestamp without time zone) AND ((a)::text = 'LM3526'::text))


EXPLAIN 
SELECT DISTINCT ON (a) a, created_at time, b, c
FROM table_first
WHERE created_at >= timestamp '2022-02-13 00:00:00' AND
      a IN ('LK0601', 'DT9834', 'LM3526')
ORDER BY a, time DESC;


QUERY PLAN


Unique  (cost=23.04..23.06 rows=4 width=74)

  ->  Sort  (cost=23.04..23.05 rows=4 width=74)

        Sort Key: a, created_at DESC

        ->  Seq Scan on table_first  (cost=0.00..23.00 rows=4 width=74)

              Filter: ((created_at >= '2022-02-13 00:00:00'::timestamp without time zone) AND ((a)::text = ANY ('{LK0601,DT9834,LM3526}'::text[])))
a	time	b	c
DT9834	2022-03-03 10:00:00	2	3
LK0601	2022-03-03 10:00:00	1	3
LM3526	2022-03-03 10:00:00	3	3