Prev: XML - Eliminate Namespace in Elements
Next: Dealing with BAD Dates in SSIS
From: Erland Sommarskog on 28 Jun 2010 17:46
tshad (tfs(a)dslextreme.com) writes:
> Maybe I am missing something here, but which expression appears twice in
> this query?

A


But I see now that the query is incorrect. That WHERE clause is a
truism.

Here is a corrected version which also displays the two instances of
A more clearly.

WITH A AS (
SELECT P.sFirstName,
P.sLastName,
PE.nProductID,
CONVERT(varchar,PE.dVisitDate,101),
PE.dVisitDate
From Person P
Join PersonEvent PE on P.PersonID = PE.PersonID
WHERE ...
)
SELECT sFirstname,
sLastName,
nProductID,
VisitDate,
dVisitDate
FROM A A1
WHERE dStart = (Select Max(dStart) from A A2
WHERE A1.sFirstName = A2.sFirstName
and A1.sLastName = A2.sLastName
and A1.nProductID = A2ProductID)



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2 3
Prev: XML - Eliminate Namespace in Elements
Next: Dealing with BAD Dates in SSIS