CODEWITHSUNDEEP
mysqlsql
r10
r10

Reputation: 13

sorting with Order By in mysql

I am using mysql as database and i have a table like the one below. 

CREATE TABLE IF NOT EXISTS `logins` (  
    `id` int(255) NOT NULL AUTO_INCREMENT,  
    `userid` varchar(255) NOT NULL,  
    `date` varchar(255) NOT NULL,  
    `status` varchar(255) NOT NULL,  
    KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=346 ;

I want to sort mysql results with order by.The problem is when i use this sql it takes only the first recod of date. Which is an older date. I want the newest date. last login date of user.

SELECT * FROM `logins` WHERE `status`='valid' GROUP BY `userid` ORDER BY `date` DESC

Any suggestions?

Upvotes: 0

Views: 44

Answers (2)

user327961
user327961

Reputation: 2490

You almost had it. Assuming id and userId doesn't evolve from one login to another, asking the MAX date should give you the expected result.

SELECT id, userId, MAX(`date`) AS lastDate, 'valid'
FROM `logins`
WHERE `status`='valid'
GROUP BY `userid`
ORDER BY `lastDate` DESC

Please note that you would need a JOIN if there were data that change between logins in the table.

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

To do this you use a sub query to get the latest record for each user id and then join that to the logins table to get the rest of the details

SELECT logins.*
FROM logins
INNER JOIN
(
    SELECT userid, MAX(`date`) AS max_date
    FROM `logins`
    WHERE `status` = 'valid' 
    GROUP BY `userid` 
) sub0
ON logins.userid = sub0.userid
AND logins.`date` = sub0.max_date
WHERE `status` = 'valid'

Upvotes: 1

Related Questions