Problem With ORDER BY DESC and UNION ALL

Look up the term "attribute splitting"; it is a design flaw in which
you take the values of an attribute in an entity and make them into
separate tables. For example, you might destroy the integrity of the
Personnel table and split it on gender to get MalePersonnel and
FemalePersonnel tables by splitting on the sex_code.

The error is usually more subtle, such has splitting on a temporal or
spatial attribute, to mimic old physical files.

A symptom of this error is two or more tables with the same structure
and data domains.
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

Please feel free to explain the best approach to the following scenario.

Table tblTasks contains Auto Incrementing TaskIDs
When the task is completed, a CompletedStatus is set to true.

The requirement is to display tasks in a grid in the order of oldest
incomplete task to newest incomplete task, followed by most recent completed
task to oldest completed task.

Even though I have been a full time Software Developer for 24 years, I am
always happy to benefit from the knowledge of others.

"--CELKO--" wrote:

> Look up the term "attribute splitting"; it is a design flaw in which
> you take the values of an attribute in an entity and make them into
> separate tables. For example, you might destroy the integrity of the
> Personnel table and split it on gender to get MalePersonnel and
> FemalePersonnel tables by splitting on the sex_code.
>
> The error is usually more subtle, such has splitting on a temporal or
> spatial attribute, to mimic old physical files.
>
> A symptom of this error is two or more tables with the same structure
> and data domains.
>
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

TiVo wrote on Tue, 29 Jul 2008 01:32:02 -0700:

> If I run the following SQL:
> CREATE TABLE [dbo].[tblOdds]([Col1] [int] NOT NULL) ON [PRIMARY]

> INSERT INTO [tblOdds] ([Col1]) VALUES (1)
> INSERT INTO [tblOdds] ([Col1]) VALUES (3)
> INSERT INTO [tblOdds] ([Col1]) VALUES (5)

> CREATE TABLE [dbo].[tblEvens]( [Col1] [int] NOT NULL ) ON [PRIMARY]

> INSERT INTO [tblEvens] ([Col1]) VALUES (2)
> INSERT INTO [tblEvens] ([Col1]) VALUES (4)
> INSERT INTO [tblEvens] ([Col1]) VALUES (6)

> SELECT Col1
> FROM (SELECT TOP 100 PERCENT Col1
> FROM tblOdds ORDER BY Col1 DESC) AS dt1
> UNION ALL
> SELECT Col1
> FROM (SELECT TOP 100 PERCENT Col1
> FROM tblEvens ORDER BY Col1 DESC) AS dt2

> I would expect to get:
> Col1 5 3 1 6 4 2

> However what is returned is:
> Col1 1 3 5 2 4 6

> If the two SELECTs are run without the UNION ALL, I get two results
> with the expected order:
> Col1 5 3 1

> Col1 6 4 2

> Any idea how I can get the result I need? Alternatively, why should
> this be the correct behaviour?

> Thanks in advance.

The order of the results is determined by an outer ORDER BY which you do not
have, so the SQL optimiser returns the data in whatever order it deems
appropriate. The inner ORDER BY clauses are only used in conjunction with
the TOP to determine which rows to return from the subqueries, not to set
the output ordering of those rows in the final SELECT. You could use
something like this instead:

SELECT Col1 FROM (
SELECT Col1, 1 as MyOrder
FROM tblOdds
UNION ALL
SELECT Col1, 2 as MyOrder
FROM tblEvens
) as dt1
ORDER BY MyOrder, Col1 DESC


whereby you are setting an ordering column in each subquery as MyOrder, and
you sort on this first and then by Col1 DESC.

--
Dan
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

Thanks, that certainly gives me the result I need.

Interestingly, I just tried my original example (with relevant syntax
changes) on an Oracle database and it worked as expected. I'm guessing that
the behaviour that requires your solution is a Transact SQL "enhancement".

Thanks for enabling me to get on with my project...
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

Is there a sneaky way of producing:
Col1
1
3
5
6
4
2

On Oracle this is easily achieved by their equivalent of
SELECT Col1
FROM (SELECT TOP 100 PERCENT Col1
FROM tblOdds ORDER BY Col1 ASC) AS dt1
UNION ALL
SELECT Col1
FROM (SELECT TOP 100 PERCENT Col1
FROM tblEvens ORDER BY Col1 DESC) AS dt2
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

I've discovered that if a number of rows is declared in the TOP clause rather
than 100 PERCENT, the query works like the Oracle equivalent (which does not
allow a TOP clause in version 9).

This kludge seems to work so I guess I'll have to use it until something
better comes along:
declare @sql as varchar(1024)
declare @tblOddsCount as bigint
select @tblOddsCount = Count(*) from tblOdds
declare @tblEvensCount as bigint
select @tblEvensCount = Count(*) from tblEvens
set @sql = 'SELECT Col1 FROM (SELECT TOP '
+ CONVERT(varchar(50),@tblOddsCount)
+ ' Col1 FROM tblOdds ORDER BY Col1 ASC) AS dt1 '
+ 'UNION ALL '
+ 'SELECT Col1 FROM (SELECT TOP '
+ CONVERT(varchar(50),@tblOddsCount)
+ ' Col1 FROM tblEvens ORDER BY Col1 DESC) AS dt2'
EXECUTE (@sql)
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

TiVo (jhussey@fareham.dontneedthisbit.gov.uk) writes:
> On Oracle this is easily achieved by their equivalent of
> SELECT Col1
> FROM (SELECT TOP 100 PERCENT Col1
> FROM tblOdds ORDER BY Col1 ASC) AS dt1
> UNION ALL
> SELECT Col1
> FROM (SELECT TOP 100 PERCENT Col1
> FROM tblEvens ORDER BY Col1 DESC) AS dt2

I don't know anything about Oracle, but I would be astounded to hear that
Oracle guarntees you a certain order in the output without an ORDER BY
clause. It may be that Oracle produces the output you desire by chance, and
this is something that could break in a future version of Oracle.

To wit, that is a very basic rule in SQL: a query returns a table, and this
table can be used to input to other queries. A table is an unordered set.
You can order the set, but then it is on longer a table, but a cursor in
ANSI terminology, and thus it is no longer composable. From this follows,
that if you want a certain order in the output, your query must end with
an ORDER BY clause.

You may want to check with people in an Oracle forum what they have to
say about your Oracle query.

--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

As everyone else has been saying, there is a very real difference
between the order a result set HAPPENS to be returned in, and an order
you can COUNT ON the result set returning in. If you must have a
specific order the only completely reliable way to get it is to force
it with an ORDER BY on the ENTIRE result set.

For your specific question:

SELECT 'A' as Whatever, Col1 FROM tblOdds
UNION ALL
SELECT 'B' as Whatever, Col1 FROM tblOdds
ORDER BY Whatever, Col1

Or if you just can't stand having that extra column showing, here is a
working example without it.

SELECT id
FROM (SELECT 'A' as Whatever, id FROM sysobjects WHERE id % 2 = 1
UNION ALL
SELECT 'B', id FROM sysobjects WHERE id % 2 = 0) as X
ORDER BY Whatever, id

Roy Harvey
Beacon Falls, CT

On Tue, 29 Jul 2008 02:39:08 -0700, TiVo
wrote:

>Is there a sneaky way of producing:
>Col1
>1
>3
>5
>6
>4
>2
>
>On Oracle this is easily achieved by their equivalent of
>SELECT Col1
>FROM (SELECT TOP 100 PERCENT Col1
> FROM tblOdds ORDER BY Col1 ASC) AS dt1
>UNION ALL
>SELECT Col1
>FROM (SELECT TOP 100 PERCENT Col1
> FROM tblEvens ORDER BY Col1 DESC) AS dt2
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

Thanks to all who have taken the time to explain the mechanism.

I'm not sure if it was clear that my follow up question was how to produce
the result 1 3 5 6 4 2 whereby the first part is produced ascending and the
second part descending.

I guess I may be missing an obvious solution based on the methods described
in this thread, but I can't figure out how an outer ORDER BY can be
constructed to achieve this.

How about a UNION ALL against two temporary tables which are populated from
suitably ORDERed SELECTs where one of the temp tables has a positively
incrementing auto column and the other a negatively incrementing one and the
UNION sorted on the resulting values of that column?
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

On Tue, 29 Jul 2008 07:43:02 -0700, TiVo
wrote:

>I'm not sure if it was clear that my follow up question was how to produce
>the result 1 3 5 6 4 2 whereby the first part is produced ascending and the
>second part descending.

Sorry, missed that.

SELECT id
FROM (SELECT 'A' as Whatever, id FROM sysobjects WHERE id % 2 = 1
UNION ALL
SELECT 'B', id FROM sysobjects WHERE id % 2 = 0) as X
ORDER BY Whatever,
CASE WHEN Whatever = 'A' THEN id ELSE NULL END,
CASE WHEN Whatever = 'B' THEN id ELSE NULL END DESC

Roy Harvey
Beacon Falls, CT
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

If what you meant is you wanted the odd values ascending followed by the
even values descending, then Roy's solution is what you want. If what you
meant is you want all of the values that came from the first table ascending
(whether they are even or odd or whatever) followed by all the values from
the second table descending, then the following will work in SQL 2005 or
later:

SELECT Col1
FROM (SELECT Col1,
1 AS WhichTable,
ROW_NUMBER()OVER (ORDER BY col1 ASC) AS rn
FROM tblOdds
UNION ALL
SELECT Col1,
2 AS WhichTable,
ROW_NUMBER()OVER (ORDER BY col1 DESC) AS rn
FROM tblEvens) AS x
ORDER BY WhichTable, rn

Tom

"Roy Harvey (SQL Server MVP)" wrote in message

> On Tue, 29 Jul 2008 07:43:02 -0700, TiVo
> wrote:
>
>>I'm not sure if it was clear that my follow up question was how to produce
>>the result 1 3 5 6 4 2 whereby the first part is produced ascending and
>>the
>>second part descending.
>
> Sorry, missed that.
>
> SELECT id
> FROM (SELECT 'A' as Whatever, id FROM sysobjects WHERE id % 2 = 1
> UNION ALL
> SELECT 'B', id FROM sysobjects WHERE id % 2 = 0) as X
> ORDER BY Whatever,
> CASE WHEN Whatever = 'A' THEN id ELSE NULL END,
> CASE WHEN Whatever = 'B' THEN id ELSE NULL END DESC
>
> Roy Harvey
> Beacon Falls, CT
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

Thanks Tom.
Yes, yours is the correct interpretation.

When I ran your example on SQL 2000 SP4 it produced:

Msg 195, Level 15, State 10, Line 4
'ROW_NUMBER' is not a recognized function name.
Msg 195, Level 15, State 10, Line 9
'ROW_NUMBER' is not a recognized function name.

However, based on your and Roy's postings I have come up with the following
which appears to be working nicely:

SELECT Col1 FROM
(SELECT 1 as WhichTable, Col1 FROM tblOdds
UNION ALL
SELECT 2 as WhichTable, Col1 FROM tblEvens) AS tblUnion
ORDER BY WhichTable,
CASE WHEN WhichTable = 1 THEN Col1 ELSE NULL END,
CASE WHEN WhichTable = 2 THEN Col1 ELSE NULL END DESC


"Tom Cooper" wrote:

> If what you meant is you wanted the odd values ascending followed by the
> even values descending, then Roy's solution is what you want. If what you
> meant is you want all of the values that came from the first table ascending
> (whether they are even or odd or whatever) followed by all the values from
> the second table descending, then the following will work in SQL 2005 or
> later:
>
> SELECT Col1
> FROM (SELECT Col1,
> 1 AS WhichTable,
> ROW_NUMBER()OVER (ORDER BY col1 ASC) AS rn
> FROM tblOdds
> UNION ALL
> SELECT Col1,
> 2 AS WhichTable,
> ROW_NUMBER()OVER (ORDER BY col1 DESC) AS rn
> FROM tblEvens) AS x
> ORDER BY WhichTable, rn
>
> Tom
>
> "Roy Harvey (SQL Server MVP)" wrote in message
>
> > On Tue, 29 Jul 2008 07:43:02 -0700, TiVo
> > wrote:
> >
> >>I'm not sure if it was clear that my follow up question was how to produce
> >>the result 1 3 5 6 4 2 whereby the first part is produced ascending and
> >>the
> >>second part descending.
> >
> > Sorry, missed that.
> >
> > SELECT id
> > FROM (SELECT 'A' as Whatever, id FROM sysobjects WHERE id % 2 = 1
> > UNION ALL
> > SELECT 'B', id FROM sysobjects WHERE id % 2 = 0) as X
> > ORDER BY Whatever,
> > CASE WHEN Whatever = 'A' THEN id ELSE NULL END,
> > CASE WHEN Whatever = 'B' THEN id ELSE NULL END DESC
> >
> > Roy Harvey
> > Beacon Falls, CT
>
>
>
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

SQL Server 2005?

Specificaly to your case , the following gives you an output , however i
think you will have more safety if you would use a cursor

WITH cte1
AS

(

SELECT Col1,ROW_NUMBER()OVER (ORDER BY col1 DESC) AS rn

FROM tblOdds

),cte2

AS

(

SELECT Col1,ROW_NUMBER()OVER (ORDER BY col1 DESC)AS rn

FROM tblEvens

) SELECT * FROM cte1

UNION ALL

SELECT * FROM cte2









"TiVo" wrote in message

> If I run the following SQL:
> CREATE TABLE [dbo].[tblOdds]([Col1] [int] NOT NULL) ON [PRIMARY]
>
> INSERT INTO [tblOdds] ([Col1]) VALUES (1)
> INSERT INTO [tblOdds] ([Col1]) VALUES (3)
> INSERT INTO [tblOdds] ([Col1]) VALUES (5)
>
> CREATE TABLE [dbo].[tblEvens]( [Col1] [int] NOT NULL ) ON [PRIMARY]
>
> INSERT INTO [tblEvens] ([Col1]) VALUES (2)
> INSERT INTO [tblEvens] ([Col1]) VALUES (4)
> INSERT INTO [tblEvens] ([Col1]) VALUES (6)
>
> SELECT Col1
> FROM (SELECT TOP 100 PERCENT Col1
> FROM tblOdds ORDER BY Col1 DESC) AS dt1
> UNION ALL
> SELECT Col1
> FROM (SELECT TOP 100 PERCENT Col1
> FROM tblEvens ORDER BY Col1 DESC) AS dt2
>
> I would expect to get:
> Col1
> 5
> 3
> 1
> 6
> 4
> 2
>
> However what is returned is:
> Col1
> 1
> 3
> 5
> 2
> 4
> 6
>
> If the two SELECTs are run without the UNION ALL, I get two results with
> the
> expected order:
> Col1
> 5
> 3
> 1
>
> Col1
> 6
> 4
> 2
>
> Any idea how I can get the result I need? Alternatively, why should this
> be
> the correct behaviour?
>
> Thanks in advance.
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL