Skip to content
Advertisement

MySql Group by but still show all rows

I have the following database

ID imagename testresult sampleref uploadedat orderid
1 filename1.png NULL ABC123 2021-08-19 12:00 1002
2 filename2.png NULL ABC123 2021-08-19 13:00 1001
3 filename3.png NULL ABC123 2021-08-19 14:00 1002
4 filename4.png NULL ABC123 2021-08-19 15:00 1001

And i am using the SQL query

SELECT * 
FROM results 
WHERE imagename IS NOT NULL 
  AND testresult IS NULL 
  AND sampleref LIKE 'LAT%' 
ORDER BY testresult IS NULL DESC, uploadedat 
LIMIT 25

However I want to group by (but not aggregate group by) the orderid, so that the result would be displayed as

ID imagename testresult sampleref uploadedat orderid
1 filename1.png NULL ABC123 2021-08-19 12:00 1002
3 filename3.png NULL ABC123 2021-08-19 14:00 1002
2 filename2.png NULL ABC123 2021-08-19 13:00 1001
4 filename4.png NULL ABC123 2021-08-19 15:00 1001

Here we are initially sorted by uploadedat but then showing the rest of the results with matching orderid essentially grouping them together but still showing each row.

I can’t use ORDER BY orderid, uploadedat as this would put rows 2 & 4 above 1 & 3

What is the solution to this? is it using 2 select statements, the first selecting * with a group by on orderid and orderby uploadedat and then the 2nd with no grouping where orderid = $Row[‘orderid’]? or is there a way to do a JOIN or double Select in a single statement for this?

Thanks?

Advertisement

Answer

You want to sort first by the min uploadedat for each orderid.

If your version of MySql is 8.0+ you can use MIN() window function:

SELECT * 
FROM results
WHERE imagename IS NOT NULL 
  AND testresult IS NULL 
ORDER BY MIN(uploadedat) OVER (PARTITION BY orderid),
         uploadedat

For previous versions use a correlated subquery:

SELECT r1.* 
FROM results r1
WHERE r1.imagename IS NOT NULL 
  AND r1.testresult IS NULL 
ORDER BY (SELECT MIN(r2.uploadedat) FROM results r2 WHERE r2.orderid = r1.orderid),
         r1.uploadedat

See the demo.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement