Simple query thats driving me nuts :)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Simple query thats driving me nuts :)

Author

Topic

salocorgan69
Starting Member

8 Posts

Posted - 2006-12-04 : 04:39:02

Hi there sql masters.

I have a table that looks similar to this:

CREATE TABLE [dbo].[lift] (
[LiftID] [uniqueidentifier] NOT NULL ,
[IsDeleted] [bit] NOT NULL ,
[RoundID] [uniqueidentifier] NOT NULL ,
[Distance] [int] NOT NULL ,
[EDate] [datetime] NOT NULL
)

So all these "lifts" are grouped by RoundID (which is a FK). Within each group of lifts, they are logically grouped by date (EDate). What I'm trying to work out is how to view the distance, per ROUND, for the first lift.

In other words, a set of distinct RoundIDs, joined to the lift table, where the lift row that its joined to is the first lift of the round, as determined by Edate (select top 1 distance from lift order by edate).

I cant seem to put these two elemnents together in one nice select statement. Can someone help me out?

Thanks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 04:42:15

Yes.
Provide some sample data and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

salocorgan69
Starting Member

8 Posts

Posted - 2006-12-04 : 04:55:28

If the table had the following rows:

LiftID, IsDeleted ,RoundID, Distance,EDate

80DD81B6-0DB5-4468-A427-A734B1C838CE, 0, 6CA148D3-4E4A-4696-976A-EEFF893E1AFB, 10, 1 jan 2006
E00E4C7B-3A1D-4541-86D8-E469EC7B046D, 0, 6CA148D3-4E4A-4696-976A-EEFF893E1AFB, 20, 2 jan 2006
CD2E85B3-AD1B-402F-AE90-D12C82754A71, 0, 6CA148D3-4E4A-4696-976A-EEFF893E1AFB, 30, 3 jan 2006

0A1DE6EC-9958-4681-BD8D-65AE2539D8AC, 0, 657F2649-0F99-4BA3-B18F-36C4D28FCB18, 11, 1 jan 2006
BD572C09-56E9-40A7-8CF2-D78E30F67672, 0, 657F2649-0F99-4BA3-B18F-36C4D28FCB18, 22, 2 jan 2006
67BF167E-2D6E-46C7-9524-44651DCF4BDB, 0, 657F2649-0F99-4BA3-B18F-36C4D28FCB18, 33, 3 jan 2006

it would represent 2 "rounds", comprising 3 "lifts" each.

I would be looking for the first (time wise according to EDate) lift of each round, and its distance. So the output would be :

RoundID, EDate, Distance

6CA148D3-4E4A-4696-976A-EEFF893E1AFB, 1 jan 2006, 10
657F2649-0F99-4BA3-B18F-36C4D28FCB18, 1 jan 2006, 11

Thanks again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 04:57:38

select t1.roundid, t1.edate, t1.distance
from yourtablenamehere t1 where t1.edate = (select min(t2.edate) from yourtablenamehere t2 where t2.roundid = t1.roundid)

Peter Larsson
Helsingborg, Sweden

salocorgan69
Starting Member

8 Posts

Posted - 2006-12-04 : 05:16:11

That would work, except that the EDates are not unique. Executing that query I get more rows in the output than there should be, as there are multiple output rows for the same round.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 05:19:05

You mean you have more than one record with same date for a roundid?

Peter Larsson
Helsingborg, Sweden

salocorgan69
Starting Member

8 Posts

Posted - 2006-12-04 : 05:25:09

I didnt expect so, but as it turns out, yes, in the real data there is that. So I'm probably looking for a solution (probably a variant of your sub query idea) that joins on the liftID itself.

Thanks Peter :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 05:26:41

What about distance? Is that always increasing by the date?
Ie lowest distance is always first record?

Peter Larsson
Helsingborg, Sweden

salocorgan69
Starting Member

8 Posts

Posted - 2006-12-04 : 05:30:05

No such assumption can be made (in fact, finding that out happens to be the motivation behind my query).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 05:56:24

So if there are two records for Jan 1, 2006 for a RoundID, any one LiftID is acceptable to return?

Peter Larsson
Helsingborg, Sweden

salocorgan69
Starting Member

8 Posts

Posted - 2006-12-04 : 05:59:52

Correct - the undefined behaviour that sql server would use to sort 2 equal records is fine.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 06:00:19

[code]
SELECT q.RoundID,
q.eDate,
q.Distance
FROM YourTableNameHere q
WHERE q.LiftID IN (
SELECT MIN(w.LiftID) mid
FROM (
SELECT RoundID,
MIN(eDate) med
FROM YourTableNameHere
) q
INNER JOIN YourTableNameHere w ON w.RoundID = q.RoundID AND w.eDate = q.med
GROUP BY w.RoundID
)[/code]

Peter Larsson
Helsingborg, Sweden

salocorgan69
Starting Member

8 Posts

Posted - 2006-12-04 : 06:15:41

Hi Peter.

Thanks for your idea!
This query doesnt compile though :

* MIN() cannot be performed on uniqueidentifier
* RoundID is invalid in the select clause for the inner most query (q) because it is not contained in an aggregate function and there is no GROUP BY clause.

I fixed the latter by adding "GROUP BY WR_RoundID" to (q), but the former problem still remains.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 06:23:26

[code]-- prepare test data
declare @test table (LiftID uniqueidentifier, IsDeleted tinyint, RoundID uniqueidentifier, Distance tinyint, EDate datetime)

insert @test
select '80DD81B6-0DB5-4468-A427-A734B1C838CE', 0, '6CA148D3-4E4A-4696-976A-EEFF893E1AFB', 10, '1 jan 2006' union all
select 'F0DD81B6-0DB5-4468-A427-A734B1C838CE', 0, '6CA148D3-4E4A-4696-976A-EEFF893E1AFB', 10, '1 jan 2006' union all
select 'E00E4C7B-3A1D-4541-86D8-E469EC7B046D', 0, '6CA148D3-4E4A-4696-976A-EEFF893E1AFB', 20, '2 jan 2006' union all
select 'CD2E85B3-AD1B-402F-AE90-D12C82754A71', 0, '6CA148D3-4E4A-4696-976A-EEFF893E1AFB', 30, '3 jan 2006' union all
select '0A1DE6EC-9958-4681-BD8D-65AE2539D8AC', 0, '657F2649-0F99-4BA3-B18F-36C4D28FCB18', 11, '1 jan 2006' union all
select 'BD572C09-56E9-40A7-8CF2-D78E30F67672', 0, '657F2649-0F99-4BA3-B18F-36C4D28FCB18', 22, '2 jan 2006' union all
select '67BF167E-2D6E-46C7-9524-44651DCF4BDB', 0, '657F2649-0F99-4BA3-B18F-36C4D28FCB18', 33, '3 jan 2006'

-- do the work
SELECT t.RoundID,
t.eDate,
t.Distance
FROM @Test t
WHERE t.LiftID = (SELECT TOP 1 LiftID FROM @Test q WHERE q.RoundID = t.RoundID ORDER BY q.eDate)[/code]

Peter Larsson
Helsingborg, Sweden

salocorgan69
Starting Member

8 Posts

Posted - 2006-12-04 : 06:39:14

Perfect :) Thanks! And nice and elegant.

I'm slightly confused about one thing though : In my table I have ~30000 lift records. There are 45 unique roundIDs. This query correctly outputs 45 records.

But to my mind, it says "for each of the 30000 lift records, find the lift record with a matching roundid, and retrieve the record with the smallest edate. use that liftid to find dereference the output columns".

Why dont I see 30000 rows in the output? Where is the "distinct" coming in?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 07:26:34

You are correct in that the correlated subquery finds ONE record. It matches the roundid, and then sorts all records with that roundid according to edate. for the first (earliest) edate, the query fetches the LIFTID.
This is done for every record in the table.
And only return those records for which the correlated subquery returns same liftid.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources