Aggregate rows in MySQL ignoring NULL values into a single row

Question

Given a table

id  date        job    color  qty
1   2018-12-10  12345  green  1
2   2018-12-11  12345        
3   2018-12-15  12345       
4   2018-12-21  12345  red    
5   2018-12-21  12345         4
6   2018-12-22  12345

The id column is auto incrementing and is the tables primary key.

A simple query

SELECT * FROM `table` WHERE `job` = '12345' ORDER BY `id` ASC;

Would return all records for the job 12345 in the order they were inserted.

Question: How would I query the table to only return a single row with the most recent values from each column?

The desired row would look like this

6   2018-12-22  12345  red    4

Madhur Bhaiya · Accepted Answer · 2018-12-10 10:53:08Z

2

One way is using Group_Concat() with Substring_Index() to extract the latest non-null value. GROUP_CONCAT() would ignore the null values.

SELECT 
  MAX(id) AS id, 
  MAX(date) AS date, 
  MAX(job) AS job, 
  SUBSTRING_INDEX(GROUP_CONCAT(color ORDER BY id DESC), ',', 1) AS color, 
  SUBSTRING_INDEX(GROUP_CONCAT(qty ORDER BY id DESC), ',', 1) AS qty 
FROM `table` 
WHERE `job` = '12345';

One assumption in the above query is that the maximum value of date corresponds to maximum id value.

Also, since we are only concerned with the "latest" value (fetching only one value); we will not be limited by the group_concat_max_len variable.

answered Dec 10, 2018 at 10:53

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Masood Bhat · Accepted Answer · 2018-12-10 10:50:34Z

0

Use LIMIT in your query as

SELECT * FROM `table` WHERE `job` = '12345' ORDER BY `id` DESC LIMIT 1;

answered Dec 10, 2018 at 10:50

Masood Bhat

1041 silver badge9 bronze badges

Comments

Tim Biegeleisen · Accepted Answer · 2018-12-10 10:53:56Z

0

You may use a separate subquery to find the max value in each separate column:

SELECT
    (SELECT MAX(id) FROM yourTable) AS id,
    (SELECT MAX(date) FROM yourTable) AS date,
    (SELECT MAX(job) FROM yourTable) AS job,
    (SELECT MAX(color) FROM yourTable) AS color,
    (SELECT MAX(qty) FROM yourTable) AS qty;

Demo

I have no idea why you want to do this, and it might indicate bad database design/planning on your part.

edited Dec 10, 2018 at 10:53

answered Dec 10, 2018 at 10:45

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

3 Comments

Madhur Bhaiya Over a year ago

Question is not highest-n-per-group problem :-) Do look at the sample data and expected output again.

Tim Biegeleisen Over a year ago

@MadhurBhaiya I was expecting you to appear, and so was covering my bases. Get ready for the OP to change the question :-)

user2399035 Over a year ago

OP wants to pick the highest value for each column and combine them in a "fake" row

Michał Turczyn · Accepted Answer · 2018-12-10 11:07:05Z

0

You could use subqueries to get desired result:

select max(id) id, 
       job, 
       max(`date`) `date`,
      (select qty from tbl
      where qty is not null
       and tbl.job = job
      order by `date` desc
      limit 1) qty,
      (select color from tbl
      where color is not null
        and tbl.job = job
      order by `date` desc
      limit 1) color
from tbl
group by job

answered Dec 10, 2018 at 11:07

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

Aggregate rows in MySQL ignoring NULL values into a single row

4 Answers 4

Comments

Comments

Demo

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related