CODEWITHSUNDEEP
mysqldatabase
Agamemnus
Agamemnus

Reputation: 1426

Mysql: inserting semi-distinct entries into another table

I have a table called logs.

logs has a few columns, two of which are called user_id, a signed integer, and timestamp, which is a timestamp.

The logs table can have several timestamps per user_id.

I want to transfer over this data to a table called users, which has several columns, two of which are called user_id and last_date.

What I need to do is to populate the users table's last_date column with, you guessed it, the last timestamp of the logs table, for each user_id.

I know how to insert a selection from a table but I can't figure out how to get a list of distinct user_ids along with their timestamps, filtered out by the last timestamp.

E.G.

SELECT DISTINCT (user_id), timestamp FROM logs GROUP BY timestamp DESC

Upvotes: 0

Views: 20

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

First, you do not need distinct when using group by. Second, you want to aggregate by user_id. Third, you need an aggregation function for timestamp:

SELECT user_id, max(timestamp)
FROM `log`
GROUP BY user_id
ORDER BY max(timestamp) desc;

I'm not sure if the final order by is needed, but it conforms to the query in the question.

This assumes that "last timestamp" means the largest time stamp. If this isn't the case, then edit your question with sample data.

Upvotes: 2

Jevgenijs Vaikulis
Jevgenijs Vaikulis

Reputation: 686

SELECT user_id, MAX(timestamp) as last_date FROM `logs` GROUP BY user_id;

Upvotes: 1

Related Questions