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.

DISTINCT multiple fields

Discussion in 'MySQL' started by Weirfire, Nov 2, 2005.

  1. #1
    I have a table that contains fields for month and year (Don't ask me why :) )

    What I want to do is select a DISTINCT year with a month so that I get

    Oct 2005
    Sept 2005
    Aug 2005

    as well as

    Oct 2004
    Sept 2004

    etc etc

    Would I query the database with something like

    SELECT DISTINCT(month, year) ??
     
    Weirfire, Nov 2, 2005 IP
    Roze likes this.
  2. Roze

    Roze Guest

    Messages:
    403
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    0
    #2
    distinct month, distinct year :)
     
    Roze, Nov 2, 2005 IP
  3. Roze

    Roze Guest

    Messages:
    403
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    0
    #3
    sorry let me be more specific

    select DISTINCT month, DISTINCT year from table

    (i dont know if the caps do anything except make the code faster to read)
     
    Roze, Nov 2, 2005 IP
  4. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #4
    Thanks Roze I'll try that out :)
     
    Weirfire, Nov 2, 2005 IP
  5. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #5
    Nah it comes up with an error :(

    Sure it's not something like DISTINCT(month,year) ?
     
    Weirfire, Nov 2, 2005 IP
  6. Roze

    Roze Guest

    Messages:
    403
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    0
    #6
    sorry...the reason I answered is because I just did it a few minutes ago in a script and it worked for me, I'll let a real pro answer :(
     
    Roze, Nov 2, 2005 IP
    Weirfire likes this.
  7. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #7
    Really??

    Much appreciated the help Rozey :)

    I tried it with 1 DISTINCT and it worked but 2 made it error when trying to fetch the data.
     
    Weirfire, Nov 2, 2005 IP
  8. Roze

    Roze Guest

    Messages:
    403
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    0
    #8
    yeah you'll never believe this, haha...That's what I had to do too, I had tried the multiple distinct's and it didn't work, and had just convinced myself that it did.

    I ended up doing one distinct and putting a second query inside the distinct and then applied the second filter. Looks like we could both use the answer!

    ps I like it when people call me Rosie. Hey i just hit double green thanks to my stupid incorrect answer. -Rose
     
    Roze, Nov 2, 2005 IP
  9. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #9
    :)

    I'm searching Google at the moment but the daft people that put info there havent got my answer yet! How inconsiderate!!!

    I'll post here when I find the answer because I will find the answer!! ;)
     
    Weirfire, Nov 2, 2005 IP
  10. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #10
    Possibly

    SELECT DISTINCT month FROM table
    UNION
    SELECT DISTINCT year FROM table

    Just going to try it now! :)
     
    Weirfire, Nov 2, 2005 IP
  11. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #11
    I did it!!! :D

    SELECT month, year FROM table GROUP BY month ORDER BY year DESC, month DESC
     
    Weirfire, Nov 2, 2005 IP
  12. J.D.

    J.D. Peon

    Messages:
    1,198
    Likes Received:
    65
    Best Answers:
    0
    Trophy Points:
    0
    #12
    Keep in mind - this syntax is MySQL-specific and won't work with other databases. If you want to keep it portable, change the SQL to this:

    SELECT month, year FROM table GROUP BY year, month ORDER BY year DESC, month DESC

    The rule of thumb is to have in the select list (i.e. what follows SELECT) only columns that are mentioned in GROUP BY and aggregate functions, such as MIN, MAX, COUNT, etc.

    J.D.
     
    J.D., Nov 8, 2005 IP
    Weirfire likes this.
  13. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #13
    Thanks JD... good to know :)
     
    Weirfire, Nov 8, 2005 IP