Select sorting problem

Hannu · #1 (**permalink**) 02-14-05, 06:15

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

r937 · #2 (**permalink**) 02-14-05, 08:19

your swenglish is pretty good

you say you read the manual but i'm guessing you didn't read this:

Quote:

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?

Hannu · #3 (**permalink**) 02-14-05, 09:17

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

r937 · #4 (**permalink**) 02-14-05, 09:24

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 )

Hannu · #5 (**permalink**) 02-14-05, 11:40

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

r937 · #6 (**permalink**) 02-14-05, 11:54

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

Hannu · #7 (**permalink**) 02-14-05, 14:31

*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*

Internet Services

Web Development

Digital Marketing

Consumer Tech