Oldie but goodie - SQL Server Forums

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

SQL Server Development (2000)

Oldie but goodie

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-02 : 16:45:52

There may be more than one way to solve the following, I was hoping to collect a few posts from readers with different solutions.

CREATE MyTable (
MyData varchar (100),
MyTime datetime
)

Create a query that builds a recordset that contains
MyData T1 T2
---------------------
MyData1, T1, T2
MyData2, T2, T3
MyData3, T3, T4
MyDataN, TN, NULL

Where T(n) < T(n+1)

I responded to a thread recently that needed a solution like this, and this is the query I came up with..

SELECT MyData, MyTime as T1,
(SELECT MIN(A.MyTime) FROM MyData A WHERE A.MyTime > MyTime) AS T2

FROM MyData

I remember seeing another method using a cross-join somewhere - maybe it's more efficient?

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-02 : 17:48:53

One other way would be:

select A.MyData,
A.MyTime as T1,
MIN(B.MyTime) as T2
FROM
MyTable A
LEFT OUTER JOIN
MyTable B
ON
A.MyData = B.MyData AND
A.MyTime < B.MyTime
GROUP BY A.MyData, A.MyTime

Can't do a cross join because you need the OUTER part so you can return the last element.

- Jeff

Edited by - jsmith8858 on 06/02/2003 19:04:14

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-02 : 21:17:14

Hi Jeff and thanks for responding.

To compare execution plans I had to modify the join from your post slightly. (MyData is unique for every row). I also setup similar table alias (A, B) for each query.

I was surprised to find the execution plan on the first query is slightly better. I wouldn't have guessed the SQL compiler would convert the subquery to a join.

CREATE Table #MyTable (
MyData varchar (100),
MyTime datetime
)
create index sam on #MyTable (MyTime)

SELECT A.MyData, A.MyTime as T1,
(SELECT MIN(B.MyTime) FROM #MyTable B WHERE B.MyTime > A.MyTime) AS T2

FROM #MyTable A

select A.MyData, A.MyTime as T1, MIN(B.MyTime) as T2

FROM #MyTable A

LEFT OUTER JOIN #MyTable B

ON A.MyTime < B.MyTime

GROUP BY A.MyData, A.MyTime

I'm a novice at interpeting execution plans. But the Query cost of query 1 is 45% and query 2 is 55%. The 2nd query involves a SORT (which accounts for the longer execution plan), while the 1st query somehow avoides the sort, yet still calculates the MIN for each row.

Go figure.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources