My visits table looks like this:
| id | name | isp | created_at |
|---|---|---|---|
| 1 | Jack | Comcast | 2021-03-13 01:00:00 |
| 2 | Jack | Comcast | 2021-03-13 01:01:00 |
| 3 | Tom | Comcast | 2021-03-13 01:02:00 |
| 4 | Jill | Amazon | 2021-03-13 01:03:00 |
| 5 | Jack | Comcast | 2021-03-13 01:50:00 |
| 6 | Jill | Amazon | 2021-03-13 01:06:00 |
| 7 | Jack | Comcast | 2021-03-14 01:00:00 |
I want to group the ‘isp’ together and then order the group based on the latest created_at of the first row in each group and within in group, order by ‘created_at’ and ‘name’. The resulting table should look like
| id | name | isp | created_at |
|---|---|---|---|
| 1 | Jack | Comcast | 2021-03-13 01:00:00 |
| 2 | Jack | Comcast | 2021-03-13 01:01:00 |
| 5 | Jack | Comcast | 2021-03-13 01:50:00 |
| 7 | Jack | Comcast | 2021-03-14 01:00:00 |
| 3 | Tom | Comcast | 2021-03-13 01:02:00 |
| 4 | Jill | Amazon | 2021-03-13 01:03:00 |
| 6 | Jill | Amazon | 2021-03-13 01:06:00 |
I have the following code to order by the ‘isp’ but am stuck on how to order the group of isp based on the first ‘created_at’.
SELECT * FROM visits ORDER BY isp
Advertisement
Answer
If your version of MySql is 8.0+ use MAX() window function in the ORDER BY clause:
SELECT * FROM visits ORDER BY MAX(created_at) OVER (PARTITION BY isp) DESC, name, created_at
For earlier versions use a correlated subquery:
SELECT v1.*
FROM visits v1
ORDER BY (SELECT MAX(v2.created_at) FROM visits v2 WHERE v2.isp = v1.isp) DESC,
name, created_at
See the demo.
| id | name | isp | created_at |
|---|---|---|---|
| 1 | Jack | Comcast | 2021-03-13 01:00:00 |
| 2 | Jack | Comcast | 2021-03-13 01:01:00 |
| 5 | Jack | Comcast | 2021-03-13 01:50:00 |
| 7 | Jack | Comcast | 2021-03-14 01:00:00 |
| 3 | Tom | Comcast | 2021-03-13 01:02:00 |
| 4 | Jill | Amazon | 2021-03-13 01:03:00 |
| 6 | Jill | Amazon | 2021-03-13 01:06:00 |