how to simplify this sql

Question

I have a problem in my sql expression, I want to put different columns in a main select,these different columns has the same where clause.

Here is my sql example:

SELECT t.*,

  (SELECT monitor_value
   FROM sub_message s
   WHERE s.project_name=t.project_name
   ORDER BY monitor_time ASC
   LIMIT 1) AS project_start_val,

  (SELECT monitor_time
   FROM sub_message s
   WHERE s.project_name=t.project_name
   ORDER BY monitor_time ASC
   LIMIT 1) AS project_start_time
FROM sub_message t

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Sep 14 '20 at 05:45

Carsten Massmann · Answer 1 · 2020-09-14T05:25:16.263

0

The following should work (untested):

select t.*,
  s2.monitor_value project_start_val,  
  s2.monitor_time project_start_time
from sub_message t
LEFT JOIN (select project_name pn, min(monitor_time) pt from sub_message group by project_name) s1 ON pn=t.project_name
LEFT JOIN sub_message s2 ON s2.project_name=s1.pn and s2.monitor_time=s1.pt

You still need to LEFT JOIN twice and it will only work reliably if the columns project_name,monitor_time are unique in table sub_message. But if this condition is met it provides you with the opportunity of including further columns of s2 into the query without having to add another subquery.

edited Sep 14 '20 at 05:25

answered Sep 14 '20 at 04:50

Carsten Massmann

26,510
2
22
43

If `monitor_time` is not unique (and I doubt that this column is defined as unique) then your query will give a lot of rows instead of one row received by OP's query. – Akina Sep 14 '20 at 05:42
That is exactly what I said in my post: the combination of `project_name` and `monitor_time` needs to be unique. As I don't know the actual data I cannot really say more for sure, but it would be helpful if a numeric `primary key` was available for table `sub_message`. Then we could base the second `LEFT JOIN` on that unique key. – Carsten Massmann Sep 14 '20 at 07:38

score 0 · Accepted Answer · answered Sep 14 '20 at 05:52

Your query shows all sub messages with their oldest monitor time and value. The straight-forward approach to this is using window functions (i.e. aggregate functions with an OVER clause). These are available as of MySQL 8.

The simplest way with FIRST_VALUE:

select 
  sm.*,
  first_value(monitor_value) over oldest_first as project_start_val,
  first_value(monitor_time) over oldest_first as project_start_time
from sub_message sm
window oldest_first as (partition by project_name order by monitor_time);

You can also get the first row per project in the from clause:

select 
  sm.*,
  smm.monitor_value as project_start_val,
  smm.monitor_time as project_start_time
from sub_message sm
join
(
  select sm2.*, min(monitor_time) over (partition by project_name) as min_monitor_time
  from sub_message sm2
) smm on smm.project_name = sm.project_name and smm.monitor_time = smm.min_monitor_time;

Before MySQL 8, window functions where not available. There you had to select from the table again. E.g.:

select 
  sm.*,
  smm.monitor_value as project_start_val,
  smm.monitor_time as project_start_time
from sub_message sm
join sub_message smm
  on smm.project_name = sm.project_name
  and (smm.project_name, smm.monitor_time) in
  (
    select project_name, min(monitor_time)
    from sub_message
    group by project_name
  );

how to simplify this sql

2 Answers2