1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Sorting mysql resultset based on date

Discussion in 'Databases' started by JEET, May 24, 2020.

  1. #1
    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
    SEMrush
    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
     
    Solved! View solution.
    JEET, May 24, 2020 IP
    SEMrush
  2. #2
    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

    upload_2020-5-25_18-40-24.png

    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
     
    Last edited: May 25, 2020
    sarahk, May 24, 2020 IP
    JEET likes this.
  3. JEET

    JEET Notable Member

    Messages:
    3,356
    Likes Received:
    371
    Best Answers:
    16
    Trophy Points:
    235
    #3
    JEET, May 25, 2020 IP