I have a mysql table that contains all the subscribed plan by users.
I am trying to create a SELECT statement that will allow me to select for a particular user from the table. Also, if that user have duplicated entries, it will get the latest date of all.
For example, I want to look for John from the table and the date I should get is 2015-09-10.
ID FirstName Date
1 John 2015-05-30
2 Mary 2014-01-10
3 John 2015-09-10
4 John 2015-03-15
5 Loen 2013-12-11
How should I go about creating the SELECT statement to above the result?
If you just need the first one, you can use
LIMIT in conjunction with
SELECT * FROM `Subscribed` WHERE `FirstName` = 'John' -- Get the name John. ORDER BY `Date` DESC -- Sort results in descending order by Date. LIMIT 1 -- Limit the results to one.
The result will be ordered by date in descending order, and the
LIMIT 1 gives out the first row.
Or to be precise, you can also use Grouping Functions if you need more than one row.
SELECT `FirstName`, MAX(`Date`) FROM `Subscribed` WHERE `FirstName` = 'John' GROUP BY `FirstName`
SELECT FirstName, max(Date) FROM yourTable WHERE FirstName = 'John'
also you can use
group by to take the result for all persons not just
If you want to do this for all users:
select s.* from subscribed s join (select firstname, max(date) as maxd from subscribed group by firstname ) ss on ss.firstname = s.firstname and ss.maxd = s.date;
Query for required output:
SELECT * FROM (SELECT * FROM `Subscribed` ORDER BY `DATE` DESC) t group by firstName
Alternatively, you can use MAX and GROUP BY
SELECT firstName, MAX(date) FROM `Subscribed` group by firstName
Verify output here : http://www.sqlfiddle.com/#!9/ec5d1/1
id firstName date 3 John September, 10 2015 00:00:00 2 Mary January, 10 2014 00:00:00
Gordeon Linoff has good answer, if your ID is a primary key and always grow on time as well, you can do this SQL for better speed:
SELECT s.* FROM subscribed s, (SELECT FirstName, max(ID) maxID FROM subscribed GROUP BY 1) AS ss WHERE s.ID = ss.maxID;