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.