CODEWITHSUNDEEP
mysqlmoodle
Spyros Savvanis
Spyros Savvanis

Reputation: 71

MySQL select last row of each one user from a list of users in a table

Im trying to select from a Moodle table the last row of each user in a list. my query is

SELECT *
FROM mdl_logstore_standard_log
WHERE eventname='\\core\\event\\user_enrolment_created'
AND courseid=34
AND relateduserid IN(120,128)
GROUP BY relateduserid;`

and the table that i use is :

MariaDB [****_*****]> describe mdl_logstore_standard_log;

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | bigint(10)   | NO   | PRI | NULL    | auto_increment |
| eventname         | varchar(255) | NO   |     |         |                |
| component         | varchar(100) | NO   |     |         |                |
| action            | varchar(100) | NO   |     |         |                |
| target            | varchar(100) | NO   |     |         |                |
| objecttable       | varchar(50)  | YES  |     | NULL    |                |
| objectid          | bigint(10)   | YES  |     | NULL    |                |
| crud              | varchar(1)   | NO   |     |         |                |
| edulevel          | tinyint(1)   | NO   |     | NULL    |                |
| contextid         | bigint(10)   | NO   | MUL | NULL    |                |
| contextlevel      | bigint(10)   | NO   |     | NULL    |                |
| contextinstanceid | bigint(10)   | NO   |     | NULL    |                |
| userid            | bigint(10)   | NO   | MUL | NULL    |                |
| courseid          | bigint(10)   | YES  | MUL | NULL    |                |
| relateduserid     | bigint(10)   | YES  |     | NULL    |                |
| anonymous         | tinyint(1)   | NO   |     | 0       |                |
| other             | longtext     | YES  |     | NULL    |                |
| timecreated       | bigint(10)   | NO   | MUL | NULL    |                |
| origin            | varchar(10)  | YES  |     | NULL    |                |
| ip                | varchar(45)  | YES  |     | NULL    |                |
| realuserid        | bigint(10)   | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

My Problem with this query is that it give me the first row for every userid in list, and i want the last. i tried order by id desc but nothing changed.

Upvotes: 0

Views: 389

Answers (2)

khakishoiab
khakishoiab

Reputation: 10808

Try this but i didnt test it 

select * from mdl_logstore_standard_log where eventname='\\core\\event\\user_enrolment_created' and courseid=34 and relateduserid IN(120,128)  GROUP BY relateduserid ORDER BY id DESC LIMIT 1;

Upvotes: 0

1000111
1000111

Reputation: 13519

You can try this:

SELECT 
L.*
FROM mdl_logstore_standard_log L
INNER JOIN 
(

 SELECT 
  relateduserid,
  MAX(id) AS max_id
 FROM mdl_logstore_standard_log
 WHERE eventname='\\core\\event\\user_enrolment_created'
 AND courseid=34
 AND relateduserid IN(120,128)
 GROUP BY relateduserid
)AS t 
ON L.id = t.max_id

First getting the maximum auto increment id for those relateduserids then making an inner join between mdl_logstore_standard_log and t table would return your expected result.

Upvotes: 1

Related Questions