-- Two values, two row(s) returned:
SELECT c.FirstName,c.LastName,c.DateOfBirth
FROM dbo.Customers c
WHERE c.DateOfBirth IN
(
SELECT temp.DateOfBirth
FROM dbo.Customers temp
GROUP BY temp.DateOfBirth
HAVING (((Count(*))>1))
)
FirstName LastName DateOfBirth
Karen Williams 1977-03-18 00:00:00
Betsy Simpson 1977-03-18 00:00:00
-- Same two values, one row returned:
SELECT
a.FirstName, a.LastName, a.DateOfBirth,
b.FirstName, b.LastName, b.DateOfBirth
FROM
dbo.Customers a INNER JOIN dbo.Customers b
ON a.DateOfBirth=b.DateOfBirth
AND (a.FirstName > b.FirstName)
OR (a.FirstName=b.FirstName AND a.LastName > b.LastName)
FirstName LastName DateOfBirth FirstName LastName DateOfBirth
Karen Williams 1977-03-18 00:00:00 Betsy Simpson 1977-03-18 00:00:00
NOTE: You want to be careful with this, given the size of the targeted result-set.
No comments:
Post a Comment