|
|
Posted by Hugo Kornelis on 10/02/89 11:50
On 15 Jun 2006 05:47:43 -0700, pltaylor3@gmail.com wrote:
>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
(snip)
Hi pltaylor3,
First, why do you have a "TOP 100 PERCENT" in your query? Since 100
percent equals all rows, you're actually requestin alll rows - which is
the default if you don't specify a TOP clause at all! Get rid of it.
Next, why do you choose aliases that require you to escape the names?
Are you really that fond of typing lots of [ and ] chartacters?
Third, you might also reconsider the "vw" prefixes on your views. In SQL
Server, objects should be named for what they represent, not for how
they happen to be implemented.
Last, here's an answer to your question:
SELECT mo.OutingNumber, mo.OutingID,
COUNT(l.LapTime) AS "# of laps",
(SELECT COUNT(l2.LapTime)
FROM dbo.Outings AS mo2
INNER JOIN dbo.Laps AS l2
ON mo2.OutingID = l2.OutingID
WHERE mo2.OutingNumber = mo.OutingNumber - 1) AS "# of
laps previousouting"
FROM dbo.Outings AS mo -- [Main Outing]
INNER JOIN dbo.Laps AS l
ON mo.OutingID = l.OutingID
GROUP BY mo.OutingNumber, mo.OutingID
ORDER BY mo.OutingNumber DESC
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|