How to merge these queries and create a single query-CodePudding

I have these three working queries:

SELECT a, 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;

SELECT a, 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;

SELECT a, 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;

In these queries, I get the latest entry for the different values of a.

How can I merge them into one query?

So that it becomes something like the following:

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

From the above query, I want to get the latest rows for all the different values of a.

I am using the PostgreSQL database.

CodePudding user response：

You may use DISTINCT ON here:

SELECT DISTINCT ON (a) a, 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;

CodePudding user response：

You can combine the results using UNION:

(SELECT a, 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, 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, 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);

see: DBFIDDLE

This query will have the correct result, but will cost some more resources as the accepted answer, see: DBFIDDLE