February 28, 2014 at 4:51 pm
Not exactly a newbie, but I can't create a query that returns the last post date for one item from two tables:
table1: int address, int temperature, datetime last_update
'address' is primary key and there is only one record per address in this table.
table2: int address, int temperature, datetime last_update
multiple records per address are allowed in this table.
table2 is actually used to record trends of temperature over time, so there are thousands of records for any address in table2. table1 is only for current temperature, so there is only one record per address.
A temperature may be updated at any time, in either table. I would like to know the most recent time each address was updated from table2 and the (only) update time from table1.
table1 is straightforward enough. to get just the last update from table2 I use:
SELECT TOP 1 table2.Address,table2.last_update FROM TABLE2 ORDER BY last_update DESC
But I need one query to return each address and its last update from both tables. Thinking an outer join for table1 would work:
SELECT TABLE1.Address,TABLE1.last_update,TABLE2.last_update
FROM TABLE1 LEFT JOIN TABLE2 on TABLE1.Address=TABLE2.Address ORDER BY TABLE1.Address
It doesn't, and where I would expect a left join would have only one result row per address, I get many.
Why does this join not work, and how can I ensure that the last_update from table2 is the most recent?
thanks!!
February 28, 2014 at 6:30 pm
SELECT t1.address, t1.temperature, t1.last_update, app2.temperature temperature_t2, app2.last_update last_update_t2
FROM table1 t1
OUTER APPLY
(
SELECT TOP 1 t2.Address,t2.last_update FROM TABLE2 t2 WHERE t1.Address=t1.Address ORDER BY t2.last_update DESC
) app2
___________________________
Do Not Optimize for Exceptions!
March 1, 2014 at 10:16 am
Thanks for the reply. When I substituted actual table/column names I got syntax errors:
the first table (3000 individual records) is rt_group_status. Second table (over 80 million records) is rt_group_coverage. The columns are WEA (address) and I need the DATE_TIME column from each table, with the date_time from rt_group_coverage being the most recent.
SELECT t1.wea, t1.date_time, app2.date_time last_update_t2
FROM rt_group_status t1
OUTER APPLY
(
SELECT TOP 1 t2.wea,t2.date_time
FROM rt_group_coverage t2
WHERE t1.wea=t2.wea
ORDER BY t2.date_time DESC
) app2
I think I transcribed your query correctly, but I don't understand the 'last_update_t2' alias after 'app2.date_time', and this is a syntax error saying app2.date_time could not be bound. removing the alias (last_update_t2) gave the same error. The 'select' inside the outer apply is flagged as 'syntax error: expecting ID' and 't1.wea' inside the outer apply could not be bound.
SQL Server 2008R2, by the way.
March 1, 2014 at 10:37 am
SELECT
wea = ISNULL(s.wea,c.wea),
date_time = MAX(ISNULL(s.date_time, c.date_time)),
TableSource = MAX(CASE WHEN s.wea IS NULL THEN 'rt_group_coverage' ELSE 'rt_group_status' END)
FROM rt_group_status s
FULL OUTER JOIN rt_group_coverage c
ON c.wea = s.wea
GROUP BY ISNULL(s.wea,c.wea)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 1, 2014 at 3:03 pm
I admit I'm not sure how this query works, but it returns one record for each address, with the latest time from either table:
wea date_time TableSource
761510610000 2014-02-28 19:52:15.000 rt_group_status
227780020000 2014-02-28 18:24:21.000 rt_group_coverage
What I need is the most recent date_time from BOTH tables:
wea date_time_rt_group_status date_time_rt_group_coverage
761510610000 2014-02-28 19:52:15.000 2014-02-28 17:18:12.000
227780020000 2014-02-28 18:24:21.000 2014-02-28 12:33:51.000
How would I tweak your query for this?
March 2, 2014 at 3:08 am
Very easily:
SELECT
wea = ISNULL(s.wea,c.wea),
date_time_rt_group_status = MAX(s.date_time),
date_time_rt_group_coverage = MAX(c.date_time)
FROM rt_group_status s
FULL OUTER JOIN rt_group_coverage c
ON c.wea = s.wea
GROUP BY ISNULL(s.wea,c.wea)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 2, 2014 at 1:24 pm
perfect. It was the outer join that was eluding me.
thank you!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply