SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DISTINCT problem

    Here is a current SQL statment:
    Code:
    SELECT mID, mDay FROM tbl_listings_beta WHERE (more conditions here)
    However, I would like to turn results like this:
    Code:
    mID, mDay
    1, 5
    2, 5
    3, 7
    4, 10
    5, 10
    6, 10
    To where only *one* row for each mDay is returned, like this:
    Code:
    mID, mDay
    1, 5
    3, 7
    4, 10
    Thanks a lot

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the cases where an mDay can have multiple mID's...is there significance in which mID displays? If not, give this a shot...
    Code:
    select  distinct mDay, min(mID) [mID]
    from    tbl_Listings_beta
    where   ...

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    null, don't forget, if you have an aggregate function, like MIN(), and any other non-aggregate in the SELECT list, then you must have a GROUP BY, which maybe you assumed, but it's a good idea to show it to somebody if the query involves an aggregate function

    and with GROUP BY, DISTINCT becomes redundant, since the groups are, by definition, distinct

    and those square brackets?

    strictly Microsoft, my good man, not MySQL

    sorry to rain on your parade, but good SQL requires due care and diligence...
    Code:
    select mDay
         , min(mID) `minID`
      from tbl_listings_beta 
    group
        by mDay  
    
    mDay,minID
    5,1
    7,3
    10,4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    TX
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Na, you're right, in all cases...I just plain forgot it again. I guess it's obvious that I'm still a student Thx for catching that stuff.

  5. #5
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help.

    However, I seem to get the same results when using:
    Code:
    Select mID, mDay
    From tbl_listings_beta 
    WHERE (my conditions) 
    Group By mDay
    Instead of:
    Code:
    Select min(mID) `minID`, mDay
    From tbl_listings_beta 
    WHERE (my conditions) 
    Group By mDay
    What's the purpose of the second way?

    (MySQL btw)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the purpose of the second way is this: it is the only way

    what you show as the first way is actually illegal syntax

    it doesn't make sense, and by allowing it to execute, mysql is doing you a disservice

    read 13.9.3 GROUP BY with Hidden Fields

    the relevant bit on that page is
    Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You will get unpredictable results.
    in every other database, you will get a syntax error

    mysql merrily executes it, and people wonder why the output often makes no sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks. Does the min function return the smallest value of each set of mID's per mDay?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, that's exactly how grouping works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry to bring up my old thread but...

    Here's my sql:
    Code:
    SELECT min(mID) `mID`, mDay 
    FROM mytable
    WHERE mYear=2004 AND mMonth=5 AND mArea='01B' 
    AND (mType = 'f') GROUP BY mDay
    It loads sometimes in 2-5 seconds, and sometimes it takes as much as a minute to load. (I have an ASP timer wrapped around *just* this database call) to time it.

    I have created indexes on mYear, mMonth, mArea, mDay, mType (and a couple other fields not involved in this query). Is there anything else I could do to make this faster? Why the sometimes drastic time difference?

    Keep in mind that I do not have a very good understanding of indexes. I just edited by mysql table and added those fields as indexes. Did I need to do anything else? I heard that usually fields often accessed in a WHERE claused should be indexed.

    mysql

  10. #10
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    anybody?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    disclaimer: i am not a DBA and that's what you need here, a DBA familiar with mysql tuning

    you could try creating a composite index on (mYear, mMonth, mArea, mDay, mType)

    but the symptoms (sometimes seconds, sometimes a minute) suggest a concurrency problem, not a design problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, thanks. Just to clear things up. I just created the index the other day. That makes it already apply to all my rows and everything?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, i believe so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •