Converting 2 select statements into a single statement within MySql

Question

I am using two sub-queries to fetch log_msg and log_state from the same table. I want to ask how can I convert these 2 sub queries into a single sub query so that I don't need to make call to the same table twice.

SELECT t1.objectId, t1.name, 
(SELECT a.logs FROM logTable a WHERE a.logId = t1.logId ORDER BY id DESC LIMIT 1) AS log_msg,
(SELECT a.state FROM logTable a WHERE a.logId = t1.logId ORDER BY id DESC LIMIT 1) AS log_state,
FROM table1 t1 WHERE t1.CreateDate >= '2019-12-01';

This is a [tag:greatest-n-per-group] query, which is asked every day on Stack Overflow. See for example https://stackoverflow.com/a/1313293/20860 — Bill Karwin, Mar 18 '20 at 20:37

score 1 · Accepted Answer · answered Mar 18 '20 at 20:44

1

You can join and filter on the top log record per group as follows:

select t1.objectId, t1.name, l.logs, l.state
from table1 t1
inner join logTable l
    on l.id = (select max(l1.id) from logTable l1 where l1.logId = t1.id)

answered Mar 18 '20 at 20:44

GMB

216,147
25
84
135

Converting 2 select statements into a single statement within MySql

1 Answers1