Patric (patric@webst8.com) writes:[color=blue]
> 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[/color]
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/techinf...2000/books.asp 
