CODEWITHSUNDEEP
mysqlsqldatabase
user3496193
user3496193

Reputation: 1

How to select a maximum value in group mysql table

I have 2 tables: people and log

CREATE TABLE people(id int,name varchar(20),surname varchar(20));
CREATE TABLE log(id int,log_date date);

and I need show all users with they last log. Data to test: 

INSERT INTO people VALUES (1, 'John', 'Smith'), (2, 'Elisabeth', 'Taylor'), (3, 'Peter', 'Jackson');
INSERT INTO log VALUES (1,'2014-02-20'),(1,'2014-02-22'),(1,'2014-02-25'),(1,'2014-03-12'),(1,'2014-04-03'),(2,'2014-02-20'),(3,'2014-03-23'),(3,'2014-03-27'),(3,'2014-04-01');

I have this code, so far:

SELECT name,surname,log_date    
FROM  people JOIN log USING(id)
GROUP BY id,log_date DESC;

but I don't know how can I show only one last log for each user.

Upvotes: 0

Views: 58

Answers (2)

mckurt
mckurt

Reputation: 154

SELECT people.id, name, surname, MAX(log_date) AS log_date FROM people INNER JOIN
    log ON people.id = log.id
GROUP BY people.id, name, surname

optionally add:

ORDER BY log_date DESC

if you want to sort by descending log dates in the result set

Upvotes: 1

yugesh
yugesh

Reputation: 116

            SELECT min(name), min(surname), max(log_date)
            FROM people p1 JOIN log l1
            ON p1.id=i1.id
            GROUP BY id;

Not tested but that should do.

Upvotes: 0

Related Questions