MySql Group by but still show all rows

Question

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?

Answer 1

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.