#1
  1. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111

    Request years that exist


    I feel this is a really basic request via a GROUP BY, but I must just be overlooking it...

    All I want to do is have a set of years returned that exist in a DATETIME column. This list will help PHP generate a drop-down to help the client choose what table to build/view.

    The basic of what I have... (`date` is the DATETIME column)
    Code:
    SELECT `date` FROM `invoice` GROUP BY `date`;
    Assuming there's hundreds of rows, I may want it to return a list of 3. Lets say 2015, 2016, 2017. I can always ORDER BY via MySQL/PHP...

    EDIT: Yep. My mind just clicked after I submitted...
    Code:
    SELECT YEAR(`date`) FROM `invoice` GROUP BY YEAR(`date`);
    Last edited by Triple_Nothing; September 26th, 2017 at 12:02 PM.
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,734
    Rep Power
    4288
    also
    Code:
    SELECT DISTINCT YEAR(`date`) FROM `invoice`
    it would be interesting to compare the EXPLAINs for these two approaches
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Ya. I've checked out a handful of debates out there. It seems they are rather alike, but I've seen nothing mention an answer to a thought of:
    In manner of DISTINCT, will a SELECT recognize the first item, then just ignore alikes?
    In manner of GROUP BY, would all still be recognized and processed?
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,734
    Rep Power
    4288
    Originally Posted by Triple_Nothing
    In manner of DISTINCT, will a SELECT recognize the first item, then just ignore alikes?
    no... it will return unique values... there is no "first"

    Originally Posted by Triple_Nothing
    In manner of GROUP BY, would all still be recognized and processed?
    would who? um, recognized? processed?

    dude, what happened when you tested it? ™
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    Well, my script works fine now, but the thought of looking through a list, if aiming for DISTINCT items, a human would note a value if it is the first time he/she has come across that value. He/She would then step to the next returned row, and if the value was already discovered, a human would just shrug off and discard that discovery, since it's nothing new. If using the method of GROUP BY, wouldn't all matching rows be discovered, noted, and GROUPed? I would assume DISTINCT would just discard a row if its column's value has already been discovered... I may be wrong. Just a thought since I never use DISTINCT...
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,734
    Rep Power
    4288
    your human analogy doesn't work... SQL is not row-at-a-time, it is table-at-a-time

    remember, there is no "first"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo