I'm using this query in MS SQL Server 2016:
select MAX(DATEADD(s, timecreated, '19700101 02:00:00')) AS last_active_on, courseid
from mdl_logstore_standard_log
where eventname = '\core\event\course_viewed'
group by courseid
order by last_active_on desc, courseid;
to get a list of the most recently accessed courses in Moodle:
| last_active_on | courseid |
|---|---|
| 2021-12-07 15:00:33.000 | 11450 |
| 2021-12-07 15:00:27.000 | 11365 |
| 2021-12-07 15:00:10.000 | 11363 |
| 2021-12-07 15:00:02.000 | 11068 |
| 2021-12-07 14:59:55.000 | 11430 |
| 2021-12-07 14:59:46.000 | 11171 |
| 2021-12-07 14:59:38.000 | 11413 |
| 2021-12-07 14:58:20.000 | 11362 |
| 2021-12-07 14:58:07.000 | 1 |
| 2021-12-07 14:56:36.000 | 11268 |
However, I only want the ''courseid'' column, like this:
| courseid |
|---|
| 11450 |
| 11365 |
| 11363 |
| 11068 |
| 11430 |
| 11171 |
| 11413 |
| 11362 |
| 1 |
| 11268 |
How do I exclude the ''last_active_on'' column while retaining the order that the courseid is returned when I use the "order by last_active_on desc, courseid;"?
(The timecreated column contains a unix timestamp, so I use dateadd() to format it into a "normal" date.)
CodePudding user response：
Replace the alias in the ORDER BY with the expression and remove the expression from the list after SELECT.
SELECT courseid
FROM mdl_logstore_standard_log
WHERE eventname = '\core\event\course_viewed'
GROUP BY courseid
ORDER BY max(dateadd(s, timecreated, '19700101 02:00:00')) DESC,
courseid ASC;
CodePudding user response：
You can use the column position
eg:
ORDER BY 1