|
|
Posted by Erland Sommarskog on 09/30/04 11:50
(pltaylor3@gmail.com) writes:
> This has got to be easier than I am making it out to be. I have a
> query that calls on two tables linked by unique ids. I would like to
> have the previous outing's # of laps brought into the current outing.
> An example of what the view follows
> SELECT TOP 100 PERCENT [Main Outing].OutingNumber, [Main
> Outing].OutingID, COUNT(dbo.vwLaps.LapTime) AS [# of laps]
> FROM dbo.vwOutings [Main Outing] INNER JOIN
> dbo.vwLaps ON [Main Outing].OutingID =
> dbo.vwLaps.OutingID
> GROUP BY [Main Outing].OutingNumber, [Main Outing].OutingID
> ORDER BY [Main Outing].OutingNumber DESC
First remove the TOP 100 PERCENT and ORDER BY from the view. They mean
nothing. In SQL 2000 it may seem that a SELECT from the view without
an ORDER BY always return rows according to the ORDER BY in the view,
but that is mere chance. In SQL 2005, this does not happen.
The only way to get sorted output from a query in SQL (no matter the
engine) is to have an ORDER BY clause in the SELECT statment that
outputs the data to the client.
> returning
> Outing Number Outing ID
> # of laps
> 6 {87C29BDC-A2D1-4606-A87B-D456F702BFCE} 99
> 5 {203319A9-4B2F-406A-9454-0DD3D5F86211} 37
> 4 {B6306DF5-5BF0-4456-8E06-0CD2ED8B1874} 54
> 3 {A70643F3-28C2-47CC-B623-4BD6904C2825} 95
> 2 {EB409D12-C79C-4D96-BE2A-0BD49C5BE69D} 80
> 1 {75D7793D-3239-4544-BACC-84EB564C8638} 36
> what I woud like is
> Outing Number Outing ID
> # of laps # of laps previousouting
> 6 {87C29BDC-A2D1-4606-A87B-D456F702BFCE} 99 37
> 5 {203319A9-4B2F-406A-9454-0DD3D5F86211} 37 54
> 4 {B6306DF5-5BF0-4456-8E06-0CD2ED8B1874} 54 95
> 3 {A70643F3-28C2-47CC-B623-4BD6904C2825} 95 80
> 2 {EB409D12-C79C-4D96-BE2A-0BD49C5BE69D} 80 36
> 1 {75D7793D-3239-4544-BACC-84EB564C8638} 36 0
> sorry if this is a silly question I'm a little new to this thanks for
> any help
SELECT MO.OutingNumber, MO.OutingID,
COUNT(L.LapTime) AS [# of laps],
COUNT(L2.LapTime) AS [# of prev laps],
FROM dbo.vwOutings MO
JOIN dbo.vwLaps L ON MO.OutingID = dbo.vwLaps.OutingID
LEFT JOIN (dbo.vwOutings MO2
JOIN dbo.vwLaps L2 ON MO2.OutingID = L2.OutingID)
ON MO.OutingNumber = MO2.OutingNumber + 1
GROUP BY MO.OutingNumber, MO.OutingID
Bur this is a complete guess about what your tables and data are like. If
it does not work out, please post:
o CREATE TABLE statements for your tables.
o INSERT statements with the sample data.
Some narrative how the tables are organised would help too.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|