MS SQL Server - Union Query

Author

Union Query

accyboy1981

2006-02-10, 7:23 am

Hi,

I've got two table of sales data that contain a days sales activity.
Currenty I run two separate queries:

select top 25 [timestamp], sales
from salesTableOne
where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
order by [timestamp] desc

and

select top 25 [timestamp], sales
from salesTableTwo
where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
order by [timestamp] asc

I want to do a union on both of this tables so all the data is
displayed together, however when I do a union I can only order it once
so one table is always showing incorrect data (as one query is asc and
the other desc). Is it possible to order both queries before a union?
Also it is not possible to select a more specific time in the where
clause as the data is not entered at a set time.

Any help would be much appreciated.

Thanks

Simon

Erwin Moller

2006-02-10, 7:23 am

accyboy1981 wrote:

> Hi,
>
> I've got two table of sales data that contain a days sales activity.
> Currenty I run two separate queries:
>
> select top 25 [timestamp], sales
> from salesTableOne
> where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
> order by [timestamp] desc
>
> and
>
> select top 25 [timestamp], sales
> from salesTableTwo
> where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
> order by [timestamp] asc
>
> I want to do a union on both of this tables so all the data is
> displayed together, however when I do a union I can only order it once
> so one table is always showing incorrect data (as one query is asc and
> the other desc). Is it possible to order both queries before a union?
> Also it is not possible to select a more specific time in the where
> clause as the data is not entered at a set time.
>
> Any help would be much appreciated.

Hi Simon,

That is easy.
Consider your two queries as ONE derived table, select all from it, and
order them as you like.

Syntax goes something like this:

SELECT DRV.sales FROM
(

// your first select
UNION
// your second select

) AS DRV
ORDER BY DRV.sales

Hope that helps you.

Regards,
Erwin Moller

>
> Thanks
>
> Simon

Jens

2006-02-10, 7:23 am

Don=B4t knopw if I understand you right and if the solution from Erwin
is appropiate for you, but as I understood the problem, the first
resultset has to be Sorted, then the second one and they have to
unioned together after the sort showing something like:

FromFirstResultSet1 Timestamp1
FromFirstResultSet1 Timestamp2
FromFirstResultSet1 Timestamp3
FromFirstResultSet2 Timestamp1
FromFirstResultSet2 Timestamp2
FromFirstResultSet2 Timestamp3

rather than

FromFirstResultSet1 Timestamp1
FromFirstResultSet2 Timestamp1
FromFirstResultSet1 Timestamp2
FromFirstResultSet2 Timestamp2
FromFirstResultSet1 Timestamp3
FromFirstResultSet2 Timestamp3

Or is the solution from Erwin fitting your needs ?

HTH, jens Suessmeyer.

Danny

2006-02-10, 7:23 am

If you don't want the rows intermixed by timestamp you can add a extra colum
and add it to the overall order by

select top 25 [timestamp], sales,
'st1' as SalesTable
from salesTableOne
where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
Union
select top 25 [timestamp], sales,
'st2' as SalesTable
from salesTableTwo
where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
order by SalesTable, [timestamp] asc

Also not sure what you mean by a more specific time. You can go down to 3
100's of a millisecond on a datetime column. However, it sounds more like
you are looking for a better way to indetify the exact rows you need. For
this we will need the table DDL, example data, and more information about
what rows you need.

"accyboy1981" <accyboy1981@gmail.com> wrote in message
news:1139571286.783804.102290@g47g2000cwa.googlegroups.com...
> Hi,
>
> I've got two table of sales data that contain a days sales activity.
> Currenty I run two separate queries:
>
> select top 25 [timestamp], sales
> from salesTableOne
> where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
> order by [timestamp] desc
>
> and
>
> select top 25 [timestamp], sales
> from salesTableTwo
> where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
> order by [timestamp] asc
>
> I want to do a union on both of this tables so all the data is
> displayed together, however when I do a union I can only order it once
> so one table is always showing incorrect data (as one query is asc and
> the other desc). Is it possible to order both queries before a union?
> Also it is not possible to select a more specific time in the where
> clause as the data is not entered at a set time.
>
> Any help would be much appreciated.
>
> Thanks
>
> Simon
>

Hugo Kornelis

2006-02-10, 8:23 pm

On 10 Feb 2006 03:34:46 -0800, accyboy1981 wrote:

>Hi,
>
>I've got two table of sales data that contain a days sales activity.
>Currenty I run two separate queries:
>
>select top 25 [timestamp], sales
>from salesTableOne
>where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
>order by [timestamp] desc
>
>and
>
>select top 25 [timestamp], sales
>from salesTableTwo
>where timestamp between '02/02/06 00:00:00' and '02/02/06 23:59:59'
>order by [timestamp] asc
>
>I want to do a union on both of this tables so all the data is
>displayed together, however when I do a union I can only order it once
>so one table is always showing incorrect data (as one query is asc and
>the other desc). Is it possible to order both queries before a union?

Hi Simon,

Three things.

First: don't use this date format. Is it day/month/year or
month/day/year? It doesn't matter for 2/2, but it will matter for 2/3.
Also, the japanese use a year/month/day format, so they might think that
you're looking for february 6th, 2002. The recommended formats are:
* yyyymmdd for date only (note: no interpunction!!)
* yyyy-mm-ddThh:mm:ss for date plus time (note the interpunction and the
uppercase T between date and time)
* yyyy-mm-ddThh:mm:ss.mmm (same as before, but including milliseconds).

Second: to query a date range, don't use BETWEEN. With your current
query, if your datatype is datetime you'll still miss the rows that have
a timestamp in the last second of the day (i.e. 23:59:59.003 up to and
including 23:59:59.997). On the other hand, if your datatype is
smalldatetime, you'll get rows with a timestamp of 20060203, midnight as
well, because 23:59:59 will be rounded UP to the nearest minute. You
should use
WHERE timestamp >= '2006-02-02T00:00:00'
AND timestamp < '2006-02-03T00:00:00'
(or you could use the date-only format '20060202' / '20060203')

Third: the answer to your question is to use derived tables:

SELECT timestamp, sales
FROM (SELECT TOP 25 timestamp, sales
FROM salesTableOne
WHERE timestamp >= '20060202'
AND timestamp < '20060203'
ORDER BY timestamp DESC) AS der1
UNION ALL
SELECT timestamp, sales
FROM (SELECT TOP 25 timestamp, sales
FROM salesTableTwo
WHERE timestamp >= '20060202'
AND timestamp < '20060203'
ORDER BY timestamp ASC) AS der2

>Also it is not possible to select a more specific time in the where
>clause as the data is not entered at a set time.

You mean something like this:

WHERE timestamp >= '2006-02-02T10:15:13.500'
AND timestamp < '2006-02-02T23:44:18.337'

?

--
Hugo Kornelis, SQL Server MVP

Sponsored Links