April 2, 2014 at 11:48 am
Hello Everyone
I am working on a query that seems very simple, but I just cannot seem to get it correct.
I want to be able to select Only 1 MemberAddress with the MIN(MoveDate) for each of the 3 Members. The results would be this:
Joe Smith, 2000-03-10, 1034 Sturgis Road, 115, Portland, Or, 77665
Sally Jones, 2001-01-02, 8970 Pierce Road, 25, Clear Bay, Washington, 96547
Beth Moore, 2006-05-30, 456 W. Blane Ave, NULL, Charleston, West Virgina, 56897
DECLARE @Members TABLE
(
MemberRowID INT IDENTITY(1,1) NOT NULL
,FirstName VARCHAR(20)
, LastName VARCHAR(20)
)
INSERT INTO @Members
SELECT 'Joe', 'Smith' UNION ALL
SELECT 'Sally', 'Jones' UNION ALL
SELECT 'Beth', 'Moore'
DECLARE @MemberAddress TABLE
(
AddressRowID int IDENTITY(1,1) NOT NULL
,MemberID INT
, MoveDate DATETIME
, StreetAddress VARCHAR(50)
, Suite VARCHAR(5)
, CityName VARCHAR(35)
, StateName VARCHAR(25)
, PostalCode VARCHAR(10)
)
INSERT INTO @MemberAddress
SELECT 1,'2011-12-05','123 Main Street', NULL, 'Dallas', 'Florida', '34567' UNION ALL
SELECT 1,'2008-10-15','111 Walnut Street', NULL, 'Fort Smith', 'Arkansas', '44556' UNION ALL
SELECT 1,'2014-02-11','2 Oak Ave', NULL, 'Columbus', 'Tenn', '23235' UNION ALL
SELECT 1,'2000-03-10','1034 Sturgis Road', '115', 'Portland', 'Or', '77665' UNION ALL
SELECT 1,'2005-07-30','1125 Washington St', NULL, 'Charleston', 'South Carolina', '25254' UNION ALL
SELECT 2,'2001-08-09','23548 Stewart St', NULL, 'San Diego', 'California', '99887' UNION ALL
SELECT 2,'2001-01-02','8970 Pierce Road', '25', 'Clear Bay', 'Washington', '96547' UNION ALL
SELECT 2,'2012-07-30','288 E. Jefferson St', '9', 'Pleasantville', 'Nevada', '54879' UNION ALL
SELECT 3,'2006-05-30','456 W. Blane Ave', NULL, 'Charleston', 'West Virgina', '56897' UNION ALL
SELECT 3,'2010-07-31','254 S. Washington Street', NULL, 'Fargo', 'Texas', '55887'
I am not opposed to using CTE, I really like them, but I cannot figure this one out. But I will try most anything. I would greatly appreciate any and all assistance or suggestions
Thank You in advance for all your suggestions, assistance and comments
Andrew SQLDBA
April 2, 2014 at 12:09 pm
I think what you're looking for is the ROW_NUMBER() windowing function. Try this:
WITH cte AS
(
SELECT
FirstName,
LastName,
MoveDate,
StreetAddress,
Suite,
CityName,
StateName,
PostalCode,
ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY MoveDate) AS rowNum
FROM @Members
LEFT JOIN @MemberAddress ON MemberID = MemberRowID
)
SELECT
FirstName,
LastName,
MoveDate,
StreetAddress,
Suite,
CityName,
StateName,
PostalCode
FROM cte
WHERE rowNum = 1
April 2, 2014 at 12:18 pm
Awesome setup.
Give this code a try:
;with cte as
(
select MemberID, MoveDate, StreetAddress, suite, CityName, StateName, PostalCode,
ROW_NUMBER() over (Partition by memberid order by movedate) rowNum
from @MemberAddress
)
select m.memberrowid, m.firstname, m.lastname, c.movedate, c.streetaddress, c.CityName, c.StateName, c.PostalCode
from @Members m
inner join cte c on c.MemberID = m.MemberRowID and c.rowNum = 1
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 2, 2014 at 12:53 pm
Hey kramaswamy and LinksUp
The code was prefect. Thank you both.
I was missing something so very small, it covered the page and I could not see it. 🙂
Thanks again to you both for your time, code and consideration in assisting me.
Andrew SQLDBA
April 2, 2014 at 2:59 pm
Glad it worked for you!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply