Sorting mysql resultset based on date Hi, I got 2 columns, "firstName" and "myDate" "myDate" stores date in this format: "2020-11-29" (YYYY-mm-dd format) Its a varchar column There could be multiple records for each "firstName", the date will differ. example: ('jeet', '2020-05-30'), ('jeet', '2020-05-31'), ('Lucky', '2020-05-31'), ('Lucky', '2020-06-01'), etc. I am trying to select all "firstName", but only 1 record for each name, the one with the smallest date. select * from table order by myDate asc group by firstName Problem is "myDate" is a varchar column, and I am not sure if results will be sorted correctly all the time. So I was hoping to convert myDate input to a numerical format like unix timestamp, and then sort it. How can I do this in the query? Thanks
If you have this create table `usernames` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `created` date DEFAULT NULL, PRIMARY KEY (`id`) ); insert into usernames values (null, 'jeet', '2020-05-30'), (null, 'jeet', '2020-05-31'), (null, 'Lucky', '2020-05-31'), (null, 'Lucky', '2020-06-01'); Code (markup): then this query SELECT `name`, MIN(`created`) AS `mincreated` FROM `usernames` GROUP BY `name` ORDER BY `name` Code (markup): will give you have a play at http://www.sqlfiddle.com/#!9/9ce6ba/1 Where it gets iffy is if you needed another field from usernames that was associated with that minimum person. I'll edit my example and show you. Edit: Version 2 http://www.sqlfiddle.com/#!9/9306ba/2