Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unanswered: Select sorting problem

    Hi all
    I'm new here and have been searching for my problem on this forum without any luck. Hopefully there's someone who can help out

    Anyway. I have a table called "Race"
    I'm running this Select query:
    Code:
    	Cal = "SELECT LapTime, MFName, MLName, Team, SUM(LapTime) AS LSUM FROM Race GROUP BY ManagerID ORDER BY LSUM"
    I loop through the recordset.

    It works just fine but I would like to get the Last LapTime added by every Manager. As it is now it shows the first post that each manager have set.
    I've tried many diffrent things and read the manuals for a solution but with no luck.

    The result should be something like this:

    Name | Team | Last LapTime | Total Time
    1
    2
    3
    4

    Thanks for now

    ps. hope you understood my Swenglish

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your swenglish is pretty good

    you say you read the manual but i'm guessing you didn't read this:
    Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You get unpredictable results.

    -- 12.9.3. GROUP BY with Hidden Fields
    there are a couple of ways to solve this

    what release are you on?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    4
    Hi r937.

    Ohh thanks

    I'm on MySQL 4.1.9-nt

    Ok.. Well I haven't read the hole manual as it's a bit big
    I only looked for solutions for my problem and ways to get the query to sort the LapTime.

    So that means GROUP BY "Field that is primarykey, autoinc" are no good?

    I've only been doing MySQL for a couple of months

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Hannu
    So that means GROUP BY "Field that is primarykey, autoinc" are no good?
    not exactly

    it means that if you have non-aggregates in the SELECT list, then they must all be in the GROUP BY

    your SELECT list looks like this --
    Code:
    SELECT LapTime
         , MFName
         , MLName
         , Team
         , SUM(LapTime) AS LSUM
    therefor, your GROUP BY should look like this --
    Code:
    GROUP 
        BY LapTime
         , MFName
         , MLName
         , Team
    however, while this would be syntactically correct and avoid the unpredictable results that mysql produces if you violate this rule (all other databases will give a syntax error for the "hidden" GROUP BY columns), it isn't what you want

    what you want is this --
    Code:
    select LapTime
         , MFName
         , MLName
         , Team
      from Race  as R
     where LapTime
         = ( select max(LapTime)
               from Race
              where ManagerID = R.ManagerID )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Posts
    4
    Ok.

    But where do I put the:
    Code:
    SUM(LapTime) AS LSUM
    in the Select Query?

    I've tried to add that to your suggested query but with no luck.

    Thanks for spending time on this

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Hannu
    But where do I put the:
    Code:
    SUM(LapTime) AS LSUM
    in the Select Query?
    maybe i'm confused

    do you want the latest lap time, or the sum of lap times?

    if it's both, then i misunderstood your problem
    Code:
    select MFName
         , MLName
         , Team
         , sum(LapTime) as sum_of_laptimes 
         , max(LapTime) as latest_laptime
      from Race 
    group 
        by MFName
         , MLName
         , Team 
    order 
        by sum_of_laptimes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Posts
    4

    Thumbs up

    *Hum*

    That's odd.. I think I have tried a simular code myself and it didn't work but now it does.

    Well I'm very greatfull for you assist, you did me a big favour here..

    Thanks

    By the way I think this is a great Forum *thumbs up*

Posting Permissions

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