This question is related to:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23243164.html
In the last post I got a good solution to my problem UNTIL I added more cols to the select, which forced me to add the same cols to group by, at which time I started getting "duplicate" records again. I'm defining duplicate as seeing the same CustID two or more times in a single resultset.
In the resultset below (extracted from a larger resultset), there are 4 records, but I only want 1 record. I want the record that has the latest datestamp in EITHER AddressCreate or PhoneCreate. That is the ideal. If that is really tough to do, then just the latest in either column (not compared across columns). But the most important thing is that (1) I only get a single record per custid and (2) I get the latest record per AddressCreate or PhoneCreate. And if I could get the latest comparing those 2 columns with each other, that would be the ultimate solution.
My latest query, based on previous solution, but with added SELECT items (giving me duplicates) is:
SELECT top 100 m.CustID, MAX(a.dtStamp) AS AddressCreate, MAX(p.dtStamp) AS PhoneCreate, m.FirstName, m.LastName, a.Address1, a.City, a.State, a.Zip, p.PhoneNumber AS Phone
FROM Member m (nolock)
JOIN Address a (nolock)
ON m.CustID = a.CustID
JOIN Phone p (nolock)
ON m.CustID = p.CustID
JOIN Customer c (nolock)
ON c.CustID = m.CustID
WHERE (c.CompanyID = 1 OR c.CompanyID IS NULL)
AND m.LastName = 'Foo'
AND m.Active = 1
AND m.[Primary] = 1
GROUP BY m.CustID, m.LastName, m.FirstName, a.Address1, a.City, a.State, a.Zip, p.PhoneNumber
ORDER BY m.CustID
CustID AddressCreate PhoneCreate FN LN ADDR City ST Zip Phone
==========================================================================================================
00041646 2005-05-01 00:23 2005-04-30 22:34 Jill Foo 47 Foobar AVE. Ina AK 45590 5872853239
00041646 2005-05-01 00:23 2005-04-30 22:24 Jill Foo 47 Foobar AVE. Ina AK 45590 6054348877
00041646 2005-04-30 22:55 2005-04-30 22:34 Jill Foo 91 Zoobar LN. DNP HI 77742 5872853239
00041646 2005-04-30 22:55 2005-04-30 22:24 Jill Foo 91 Zoobar LN. DNP HI 77742 6054348877
1:
2:
3:
4:
5:
6:
Select allOpen in new window
by: BriCrowePosted on 2008-03-17 at 14:10:03ID: 21146177
Scott probably had the better solution in your last post based on this new information. You'll have to use subqueries to obtain the "table of latest phone/address entries by customer" to join against. This eliminates the need for grouping. Try this and let me know if it works.
SELECT TOP 100 m.CustID,
MAX(a.dtStamp) AS AddressCreate,
MAX(p.dtStamp) AS PhoneCreate,
m.FirstName,
m.LastName,
a.Address1,
a.City,
a.State,
a.Zip,
p.PhoneNumber AS Phone
FROM Member m (nolock)
INNER JOIN (SELECT * FROM Address WHERE dtStamp =
(SELECT MAX(dtStamp) FROM Address AS B WHERE B.CustID = Address.CustID) AS a (nolock)
ON m.CustID = a.CustID
INNER JOIN (SELECT * FROM Phone WHERE dtStamp =
(SELECT MAX(dtStamp) FROM Phone AS B WHERE B.CustID = Phone.CustID) AS p (nolock)
ON m.CustID = p.CustID
INNER JOIN Customer c (nolock)
ON c.CustID = m.CustID
WHERE ISNULL(c.CompanyID, 1) = 1
AND m.LastName = 'Foo'
AND m.Active = 1
AND m.[Primary] = 1
ORDER BY m.CustID