470,594 Members | 1,415 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

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

Jul 23 '05 #1
3 1151
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)
Jul 23 '05 #2
Patric (pa****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
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

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Rob Yelvington | last post: by
1 post views Thread by shunah | last post: by
reply views Thread by Betsy | last post: by
1 post views Thread by DS | last post: by
20 posts views Thread by Stewart Graefner | last post: by
3 posts views Thread by avandenbroeck | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.