Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Am I stupid or?

Author  Topic 

Rauken
Posting Yak Master

108 Posts
Posted - 2002-10-16 : 08:50:56
I have a table called result, it looks like this:

ResultId INT
PersonId INT
ResultDate DATETIME
RaceNo INT
RaceLenght INT
RaceTime DEC

The table is filled with result for runners, each runner can have 3-10 results. Today the table consist of What I want to do is get the average of three lowest times for each runner. I've been back and forth with this, it seems like it should be an easy task with a select statement or two but I'm going nuts!

mr_mist
Grunnio

1870 Posts
Posted - 2002-10-16 : 08:56:40
I'm not sure it is that easy to be honest. Anyway you need to specify if you want any 3 lowest times for a runner, or the three lowest times for a runner in a certain race no/ race length.
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-16 : 09:01:26
This is actually a pretty tricky query to wrap your head around.

Without actual DDL and sample data, I am unable to test this query, but I think this should do it for you ...

select
    PersonId,
    avg(RaceTime) as 'avg worse 3 times'
from
    dbo.results r
where
    exists (
        select 1
        from
            dbo.results
        where
            personid = r.personid and
            racetime = r.racetime
        group by
            someid
        having (
            select
                count(*)
            from
                dbo.results
            where
                personid = r.personid and
                racetime > r.racetime ) < 3 )
order by
    personid

 


Jay White
{0}
Go to Top of Page

Rauken
Posting Yak Master

108 Posts
Posted - 2002-10-16 : 09:03:39
Thanks for the query, I'll try it out. It's just the average of the three lowest times for each person, not any specific race.
Go to Top of Page

Rauken
Posting Yak Master

108 Posts
Posted - 2002-10-16 : 09:18:50
Ok I get the following result:

Column 'r.personid' is invalid in the select list because it is not contained in an aggregate function....
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts
Posted - 2002-10-16 : 09:31:10
which it is why Page47 subtly suggested you provide enough info for him to test the query. We can't mind read, so you might have to do some debugging now

Damian
Go to Top of Page

Rauken
Posting Yak Master

108 Posts
Posted - 2002-10-16 : 09:34:30
hehe ok I understand :-)
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-16 : 09:47:50 

select
    PersonId,
    avg(RaceTime) as 'avg worse 3 times'
from
    dbo.results r
where
    exists (
        select 1
        from
            dbo.results
        where
            personid = r.personid and
            racetime = r.racetime
        group by
            personid
        having (
            select
                count(*)
            from
                dbo.results
            where
                personid = r.personid and
                racetime > r.racetime ) < 3 )
order by
    personid

 
Sorry ...

Jay White
{0}
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts
Posted - 2002-10-16 : 09:52:23 


SELECT PersonID, AVG(RaceLength) as LowAverage
FROM Result R
WHERE EXISTS 
(SELECT 1 FROM Result WHERE RaceLength <= R.RaceLength
GROUP BY PersonID HAVING COUNT(Race) <= 3)
GROUP BY PersonID



DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 10/16/2002 09:53:22
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-16 : 10:12:32
GOTO EDIT:

First off, I think you meant

SELECT PersonID, AVG(RaceLengthRaceTime) as LowAverage
FROM Result R
WHERE EXISTS 
(SELECT 1 FROM Result WHERE RaceLengthRaceTime <=>= RaceLengthRaceTime
GROUP BY PersonID HAVING COUNT(RaceRaceTime) <= 3)
GROUP BY PersonID

 
Second, looking at the execution plans and considering no indexes (again without DDL ....) it seems as though my original query is presenting a better subtree cost (0.118 vs 0.190). Your grouping in the exists is not filtered, so the sort operation is much more costly(54% or query cost). As the number of rows in the table increase, I think you'll find my query performing better and better ...

EDIT: actually, on second look, you query won't work. It will return the same average for all the racers ... the average of the three worst times across all racers ... you need to correlate the subquery on PersonID to get only those races for that person ...

Jay White
{0}

Edited by - Page47 on 10/16/2002 10:31:48
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts
Posted - 2002-10-16 : 11:31:33
Surely it should be grouped on both PersonId and RaceLength -- you wouldn't want to average someone's 200 metre time with their 100 metre time!
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2002-10-16 : 11:40:40
quote:

Surely it should be grouped on both PersonId and RaceLength -- you wouldn't want to average someone's 200 metre time with their 100 metre time!



I must admit I find that somewhat odd too. But he did say "It's just the average of the three lowest times for each person"...
Go to Top of Page

Rauken
Posting Yak Master

108 Posts
Posted - 2002-10-17 : 11:24:01
I'm from Sweden so my English might not be the best at all times. What I mean is the average of the three best times (lowest?). Thanks for all the help!
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2002-10-18 : 03:37:32
Ahh, you want the average three *fastest* times. In which case you'd probably have to switch around the > symbol in Page47's query. Or something.
Go to Top of Page

sanjnep
Posting Yak Master

191 Posts
Posted - 2002-10-18 : 06:05:51
"Heaven's light is our guide"

Sanjeevshrestha
Go to Top of Page

mr_mist
Grunnio

1870 Posts
Posted - 2002-10-18 : 06:18:06
quote:

"Heaven's light is our guide"



That's as maybe, but how does your resume answer the question?
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-18 : 06:47:37
quote:

Ahh, you want the average three *fastest* times. In which case you'd probably have to switch around the > symbol in Page47's query. Or something.


Correct ..


select
    PersonId,
    avg(RaceTime) as 'avg worsebest 3 times'
from
    dbo.results r
where
    exists (
        select 1
        from
            dbo.results
        where
            personid = r.personid and
            racetime = r.racetime
        group by
            personid
        having (
            select
                count(*)
            from
                dbo.results
            where
                personid = r.personid and
                racetime < r.racetime ) < 3 )
order by
    personid

 


Jay White
{0}

Edited by - Page47 on 10/18/2002 07:57:04
Go to Top of Page

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts
Posted - 2002-10-18 : 18:21:11
Page47 nice solution!
Just one question is it missing a group by clause since you are using the avg aggregate function????

Group By PersonID
Go to Top of Page

nivaskhan
Starting Member

17 Posts
Posted - 2002-10-23 : 08:20:04
Nice solution...
But i cant understand what it does.Figuring it out how the results output.
Can youplease be a little bit brief how each inner query works and whats its output.
That will of great help.
Thanks in advance
---------------------------
SELECT PERSONID, AVG(RACETIME) AS [AVG WORSEBEST 3 TIMES]
FROM DBO.RESULTS R
WHERE EXISTS
(
SELECT 1 FROM DBO.RESULTS
WHERE PERSONID = R.PERSONID AND RACETIME = R.RACETIME
GROUP BY PERSONID
HAVING (
SELECT COUNT(*) FROM DBO.RESULTS
WHERE PERSONID = R.PERSONID AND RACETIME < R.RACETIME
) < 3

)
GROUP BY PERSONID
ORDER BY PERSONID


Regards,
Nivas
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2002-10-23 : 08:47:01
quote:
Page47 nice solution!
Just one question is it missing a group by clause since you are using the avg aggregate function????

Group By PersonID

You are correct. My bad. Again, I stress that without DDL and sample data I am unable to test this query ....

quote:
Can youplease be a little bit brief how each inner query works


Hum...Typically, complicated set-based constructs are difficult to express in words ... basically, the correlated sub-query will return 1 for a given row if and only if there are less than three other rows in the table for the same person which have greater times.



Jay White
{0}
Go to Top of Page

Rauken
Posting Yak Master

108 Posts
Posted - 2002-10-23 : 08:56:29
Hi again!
Thanks for all help on this problem. Yes there is a group clause missing here is the correct solution:


select
    PersonId,
    avg(RaceTime) as 'avg worsebest 3 times'
from
    dbo.results r
where
    exists (
        select 1
        from
            dbo.results
        where
            personid = r.personid and
            racetime = r.racetime
        group by
            personid
        having (
            select
                count(*)
            from
                dbo.results
            where
                personid = r.personid and
                racetime < r.racetime ) < 3 )
group by
     personid
order by
    personid





Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site