473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 1224
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Rob Yelvington | last post by:
I need some help with a query. I have two tables in one data base that both have a SSN field. The ssn field in one table contains slashes and the ssn field in the other does not. I know how...
1
by: shunah | last post by:
hello. In an app I'm building, there are two different kinds of memos, family memos and individual memos. Right now I have a single form that I using in four slightly different ways: 1. add a...
0
by: Betsy | last post by:
I have a Worrd document that is merged to a query in access (both 2003). If I put a parameter into the querie that requires user input such as the document won't open because it says all the...
1
by: DS | last post by:
Any reason why a Query would stop working? If I run the Query without the Criteria froma form it works. When I run it from a form with the Criteria it doesn't work. Any know why? Thanks. DS
20
by: Stewart Graefner | last post by:
Here is a chunk of code that works for an individual record. It evaluates dates and checks or unchecks boxes as it goes along. It may not be pretty but it works. What my problem is that I need it...
3
by: avandenbroeck | last post by:
The following code does not select the appropiate fields, it select everything with in a zip code ( as an example)and so on. I have tried to modify but I am a rookie and everything I tried has...
48
by: Jimmy | last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far: <% Dim oConn, oRS, randNum Randomize() randNum = (CInt(1000 * Rnd) + 1) *...
1
by: diatel1 | last post by:
I have a database with tables and forms. One of those table is the Inventory table, where I have fields like "product" "stock #" "buy date" "paid date" "paid yes/no", etc. I would like to have a form...
4
by: dhpacheco | last post by:
Hello I’m doing an application using VS 2005, c# for management a DB, but I new using the language, neither have experience in programming. Please could someone tell me if this is correct? I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.