I want the latest list for each specified column (SQL, SELECT statement)

Please tell me the SQL statement.
How do I write a SELECT statement to get the latest bill list for each user_id?

mysql>descbills;
+---------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | YES | MUL | NUL | |
| created_at | datetime | YES | | NULL | |
+---------------------+--------------+------+-----+---------+----------------+

+------------------------------------------------------------------------
| id | user_id | created_at |
+----+---------+------------+
|  1 |     111 | 2015-01-01 |
+----+---------+------------+
|  2 |     111 | 2015-01-02 |
+----+---------+------------+
|  3 | 111 | 2015-01-03 | ← 111 Last Date
+----+---------+------------+
|  4 | 222 | 2015-01-02 | ← 222 Last Date
+----+---------+------------+
|  5 |     222 | 2015-01-01 |
+----+---------+------------+

+------------------------------------------------------------------------
| id | user_id | created_at |
+----+---------+------------+
|  3 |     111 | 2015-01-03 |
+----+---------+------------+
|  4 |     222 | 2015-01-02 |
+----+---------+------------+

mysql sql

2022-09-30 16:22

3 Answers

You have already answered, but I will try another way.

SELECT* FROM bills ASb WHERE created_at=(
  SELECT MAX(created_at) FROM bills AS bs WHERE b.user_id = bs.user_id
);

If you still have more than one record with a maximum value, all records will be selected.

add

For example, to extract the record with the highest id when you have more than one record with the maximum value:

SELECT* FROM bills ASb WHERE id=(
  SELECT MAX(id) FROM bills ASbs
  WHERE b.user_id = bs.user_id AND
        created_at=(
          SELECT MAX(created_at) FROM bills ASbs
          WHERE b.user_id=bs.user_id))ORDER BY user_id;

If you want to extract records with a minimum value of id, change the above MAX(id) to MIN(id).

2022-09-30 16:22

Do you want to re-join and find which line is affected?

SELECT bills.*
FROM bills
INNER JOIN(SELECT user_id,MAX(created_at)AS created_at
            FROM bills
            GROUP BY user_id)AS max
ON bills.user_id=max.user_id AND bills.created_at=max.created_at

As you can see from this query, user_id has only selected the maximum value of created_at, so if the maximum value exists more than one line, the result will be multiple.
If that's a problem, you need to re-group it and think about what id you want to choose.

2022-09-30 16:22

If you don't need the id column, you can get it from the SQL below.

SELECT user_id, MAX(created_at) FROM bills GROUP BY user_id

2022-09-30 16:22

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656