Re: Union Query syntax

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/02/05 

Date: Wed, 02 Feb 2005 22:07:52 +0100

On Wed, 2 Feb 2005 01:43:01 -0800, Hoof Hearted wrote:

>I want to pull the most recent record from 2 tables with a Union query.
>Here's what I thought would work:
>
>Select TOP 1 EntryTime, Windspeed, WindDirection, Temperature FROM
>BlackpoolWeather ORDER BY EntryTime Desc
>UNION Select TOP 1 EntryTime, Windspeed, WindDirection, Temperature FROM
>ManchesterWeather ORDER BY EntryTime Desc
>
>I know its the TOP 1 that causing the problem. So how do I make this work?

Hi Hoof,

Try this:

SELECT EntryTime, Windspeed, WindDirection, Temperature
FROM (SELECT TOP 1 EntryTime, Windspeed, WindDirection, Temperature
       FROM BlackpoolWeather
       ORDER BY EntryTime DESC) AS a
UNION ALL
SELECT EntryTime, Windspeed, WindDirection, Temperature
FROM (SELECT TOP 1 EntryTime, Windspeed, WindDirection, Temperature
       FROM ManchesterWeather
       ORDER BY EntryTime DESC) AS b

Wouldn't your life become much easier with one table "Weather", with City
as an extra column, also part of the primary key?

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)