Some help with a join
-
Need some help figuring out the SQL for a join. I am trying to join to tables together. Table A is my master table with a unique index. Table B has multiple records for each of those indexes with a datetime stamp. I want to do a join such that I only join the record from Table B that has the lowest date. Could somebody point me in the right direction? Thanks!
-
Try using a derived table:
SELECT *
FROM TableA A
JOIN TableB B
ON A.APK = B.APK
JOIN (
SELECT B1.APK, MIN(B1.DateCol) AS DateCol
FROM TableB B1
GROUP BY B1.APK
) D
ON B.APK = D.APK
AND B.DateCol = D.DateCol
-
Hi guys,
I'm having a slow afternoon so tried to do some performance tuning based on the above with table A(40015 rows) and table B(455990 rows)
the first solution comes back with the following
CPU Time 1532ms, Execution time 2097ms
I tried another solution here are the results
CPU Time 1187, Execution time 1427ms
CREATE TABLE #VtblDates
(
EntityID INT NOT NULL PRIMARY KEY CLUSTERED
,CreateDate DATETIME -- NONCLUSTERED
)
INSERT INTO #VtblDates
SELECT B1.ID, MIN(B1.Date) AS Date
FROM TABLEB B1
GROUP BY B1.ID
SELECT *
FROM TableA A
JOIN TABLEB B
ON A.ID = B.ID
JOIN #VtblDates D
ON B.ID = D.ID
AND B.Date = D.Date
Hope this helps
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
Thanks for the help guys!
-
Sorry all I forgot to change the column names on my last post.
This solution should work:
CREATE TABLE #tblDates
(
ID INT NOT NULL PRIMARY KEY CLUSTERED
,Date DATETIME
)
INSERT INTO #tblDates
SELECT B1.ID, MIN(B1.Date) AS Date
FROM TABLEB B1
GROUP BY B1.ID
SELECT *
FROM TableA A
JOIN TABLEB B
ON A.ID = B.ID
JOIN #tblDates D
ON B.ID = D.ID
AND B.Date = D.Date
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply