How to merge these queries and create a single query

Question

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.

Select only the database that you are working with `MySQL` and `PostgreSQL` are not the same. — Luuk, Apr 15 '22 at 06:28
I know that is beside the point, but why do people so often ask to "merge several queries into one"? What is the point? — Laurenz Albe, Apr 15 '22 at 06:38
@LaurenzAlbe I've to get this data for over 10000 values of `a`. And querying DB one time is better than doing it 10000 times. — Cool Breeze, Apr 15 '22 at 06:59
@LaurenzAlbe , what CoolBreeze said has often been also my personal opinion-based assumption. — Stefan Wuebbe, Apr 15 '22 at 09:31

score 3 · Accepted Answer · answered Apr 15 '22 at 06:27

3

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;

answered Apr 15 '22 at 06:27

Tim Biegeleisen

502,043
27
286
360

Luuk · Answer 2 · 2022-04-15T06:51:56.440

1

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

edited Apr 15 '22 at 06:51

answered Apr 15 '22 at 06:37

Luuk

12,245
5
22
33

Thanks for the answer, @Luuk. And for the Fiddles. As far as I remember, I have seen very many `Union All` Answers in the Community places, and almost no `Union` w/o "All" suggestions in general. This one also looks interesting in that context: https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all – Stefan Wuebbe Apr 15 '22 at 09:26
1

In this case the result will never be the "same" because `a` is always different. I also could have use `UNION ALL`, which might cost less (but this is a bad example to take that decision from, because not enough records... – Luuk Apr 15 '22 at 09:42
Yes, there was no criticism intended in my previous comment, BTW, I found your input rather interesting, not necessarily debatable :) – Stefan Wuebbe Apr 15 '22 at 09:49
1

No worries, it was not receive as criticism! , In fact a good question is it always to know if one should use `UNION` of `UNION ALL`, while most people do `UNION ALL` buy default, and only when needed delete the `ALL` – Luuk Apr 15 '22 at 10:02

How to merge these queries and create a single query

2 Answers2