Microsoft SQL Server forum - Need help with complex(?) querie

Author

Need help with complex(?) querie

Patric

2005-03-30, 7:04 pm

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

Hugo Kornelis

2005-03-30, 7:04 pm

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)

Erland Sommarskog

2005-03-30, 7:04 pm

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

Patric

2005-03-31, 7:01 am

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

Sponsored Links