Need help with complex(?) querie

Hi

I'm having some problem getting the following result.

I have 2 tables

Drivers
--------
DriverID int (PK)
DriverName varchar(50)

Runs
-------
RunID int (PK)
DriverID int (FK)
Speed float
ET float

Example data:

Drivers:
DriverID DriverName
-------------------
1        Nisse
2        Ken
3        Dan

Runs:
RunID DriverID Speed ET
-----------------------------
1     3        143.2 5.99
2     2        33.22 10.39
3     3        139.3 6.34
4     1        213.1 5.23
5     1        211.2 5.32


What i wan't is to get every drivers best ET sorted like this

DriverID DriverName RunID Speed ET
-------------------------------------
1        Nisse      4     213.1 5.23
3        Dan        1     143.2 5.99
2        Ken        2     33.22 10.39


Hope this is possible.

Thanks
Patric

On 30 Mar 2005 11:38:12 -0800, Patric wrote:

>Hi
>
>I'm having some problem getting the following result.
>
>I have 2 tables
>
>Drivers
>--------
>DriverID int (PK)
>DriverName varchar(50)
>
>Runs
>-------
>RunID int (PK)
>DriverID int (FK)
>Speed float
>ET float
>
>Example data:
>
>Drivers:
>DriverID DriverName
>-------------------
>1        Nisse
>2        Ken
>3        Dan
>
>Runs:
>RunID DriverID Speed ET
>-----------------------------
>1     3        143.2 5.99
>2     2        33.22 10.39
>3     3        139.3 6.34
>4     1        213.1 5.23
>5     1        211.2 5.32
>
>
>What i wan't is to get every drivers best ET sorted like this
>
>DriverID DriverName RunID Speed ET
>-------------------------------------
>1        Nisse      4     213.1 5.23
>3        Dan        1     143.2 5.99
>2        Ken        2     33.22 10.39
>
>
>Hope this is possible.
>
>Thanks
>Patric

Hi Patric,

Try if this works:

SELECT     d.DriverID, d.DriverName, r.RunID, r.Speed
FROM       Runs AS r
INNER JOIN Drivers AS d
ON   d.DriverID = r.DriverID
WHERE NOT EXISTS
(SELECT   *
FROM     Runs AS r2
WHERE    r2.DriverID = r.DriverID
AND      r2.Speed < r.Speed)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Patric (patric@webst8.com)  writes:
> Drivers
> --------
> DriverID int (PK)
> DriverName varchar(50)
>
> Runs
> -------
> RunID int (PK)
> DriverID int (FK)
> Speed float
> ET float
>
> Example data:
>
> Drivers:
> DriverID DriverName
> -------------------
> 1        Nisse
> 2        Ken
> 3        Dan
>
> Runs:
> RunID DriverID Speed ET
> -----------------------------
> 1     3        143.2 5.99
> 2     2        33.22 10.39
> 3     3        139.3 6.34
> 4     1        213.1 5.23
> 5     1        211.2 5.32
>
>
> What i wan't is to get every drivers best ET sorted like this
>
> DriverID DriverName RunID Speed ET
> -------------------------------------
> 1        Nisse      4     213.1 5.23
> 3        Dan        1     143.2 5.99
> 2        Ken        2     33.22 10.39

SELECT r.DriverID, d.DriverNme, r.RunID, r.Speed, r.ET
FROM   Runs r
JOIN   Drivers d ON r.DriverID = d.DriverID
JOIN   (SELECT DriverID, maxspeed = MAX(Speed)
FROM   Runs
GROUP  BY DriverID) AS m ON m.DriverID = r.DriverID
AND m.maxspeed = r.Speed

Notes:

1) The query in parentheses is a *derived table*. It is sort of a
temp table within the query, but the table is not necessarily
materialized, and the optimizer may recast computation order
as long the result is not affected. This is a very powerful
concept.

2) If there are two runs with the same max speed for the same driver,
both runs will be displayed. You did not specify any rules to
break ties.

3) Had you provided table definitions and sample data with CREATE TABLE
statements and INSERT statements, the query above would have been
tested. Now the query is untested.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thanks for your help. I think it solved my problem. I'll try to make a
better description with create teble and inserts next time.

/ Patric