June 23, 2009 at 8:00 am
Hello Experts,
I’m not very strong with regard to writing T-SQL code, and I have a problem that I would like help in solving. :blush:
I have written the following code that illustrates my problem. I need to return the information from tableA and tableB, but I only want one row returned for each row in tableB, with the row selected from tableA that has the newest date (if there are identical dates then I still only want 1 row returned).
-- Activity Table
CREATE TABLE #tableA (Ref INT, action_date DATETIME)
INSERT INTO #tableA VALUES (1,'2009-01-01')
INSERT INTO #tableA VALUES (2,'2008-01-01')
INSERT INTO #tableA VALUES (1,'2008-01-01')
INSERT INTO #tableA VALUES (3,'2009-02-02')
INSERT INTO #tableA VALUES (3,'2009-02-02')
-- Base Table
CREATE TABLE #tableB (Ref INT, info VARCHAR(30))
INSERT INTO #tableB VALUES (1,'Info 1')
INSERT INTO #tableB VALUES (2,'Info 2')
INSERT INTO #tableB VALUES (3,'Info 3')
-- Porblem code
SELECT *
FROM #tableB AS b WITH(NOLOCK)
LEFT JOIN #tableA AS a WITH(NOLOCK)
ON (b.ref = a.ref)
DROP TABLE #tableA
DROP TABLE #tableB
I hope this is clear, but if not I can give more clarification. Can anyone give me a hint on what I need to do with my select statement to achieve this?
Regards,
Phil
June 23, 2009 at 8:08 am
SELECT B.REF,B.INFO,MAX(A.ACTION_DATE) 'ACTION_DATE'
FROM #tableB AS b WITH(NOLOCK)
LEFT JOIN #tableA AS a WITH(NOLOCK)
ON (b.ref = a.ref)
GROUP BY B.REF,B.INFO
June 23, 2009 at 8:22 am
That has worked brilliantly.
Thank you for your help, this is a fantastic place to learn and ask questions.
Regards,
Phil
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply