Problem With ORDER BY DESC and UNION ALL

Sorry Daniel, my mistake



"Uri Dimant" wrote in message

> Daniel
> Yes (i had the same in my mind), but it does not give the OP desired
> result.
>
> "Daniel Crichton" wrote in message
>
>> 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 

Glad to hear it.
It's a nice result in this newsgroup when you not only get the answer to
your problem, but actually learn how to generate it yourself.
BTW, I don't think anyone thanked you for providing the DDL and INSERT
statements to demonstrate your problem in your original post. That is
always very helpful and we appreciate it. Thank you.
And now, you've been yelled at by CELKO, that's the ultimate validation as a
"real-world" SQL programmer. Congradulations.

Tom

"TiVo" wrote in message

> 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 

The interesting thing is not what you happened to get on the ORacle system it is what Oracle
*documents* that you should have. By definition, a table is not sorted, and a result from a SELECT
statement is a table. I.e., the DBMS is free to return the rows in any order it wants. Of course,
ORDER BY is an exception, but in your case you didn't have an ORDER BY on the *outer* query (the
result from the UNION. As I mentioned, without an ORDER BY, the RDBMS is free to return the orws in
any order it feels like. "Feels like" will in most cases translate to what the optimizer judgest is
least expensive from a performance perspective.

So, what I'm trying to say is that just because you happened to get that desired order on the Oracle
system *for this particualr query with the state of the table as they are now and the statistics you
have now and the indexes you have now* doesn't guarantee anything. Just a coniencident. Unless
Oracle deviates in some aspect from the relationalmodes and documents somewhere that it preserves
the ordering. But that fact that your current query bahaves like that isn't proof enough. I suggest
you talk to an Oracle expert of consult the documentation, else code the query so it is safe on
Oracle as well.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"TiVo" wrote in message

> 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 

TiVo wrote on Tue, 29 Jul 2008 03:34:06 -0700:

> 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)


While it might work in all the runs you've made, there's no guarantee that
it will continue to work as without an ORDER BY on the outer SELECT the SQL
engine is free to return the rows in whatever order it wants. Just because
it returns in that order today doesn't mean it will tomorrow.

Why are you so vehement in ignoring the suggestions provided that will
guarantee the sort ordering and instead continue to insist on using T-SQL
that could well end up changing it's sort order at some point in the future?
Why not just use an order on the outer query and be happy that it will work
no matter how the internal queries are handled in the optimiser?

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

On Tue, 29 Jul 2008 11:15:03 -0400, "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:

The odd/even test nonsense in my example was simply part of pretending
to have a table with the attributes of the test data posted. I didn't
intend the modulo test to be interpreted as part of the solution. I
guess I should have made that clear.

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

That's the trouble with this kind of interaction.
No wonder projects fail when they have outsourcing on different continents.
A good 10 minute chat would have worked wonders!

Thanks for your help

"Roy Harvey (SQL Server MVP)" wrote:

> On Tue, 29 Jul 2008 11:15:03 -0400, "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:
>
> The odd/even test nonsense in my example was simply part of pretending
> to have a table with the attributes of the test data posted. I didn't
> intend the modulo test to be interpreted as part of the solution. I
> guess I should have made that clear.
>
> Roy Harvey
> Beacon Falls, CT
>
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

TiVo wrote on Tue, 29 Jul 2008 08:36:06 -0700:

> 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.

This function does not exist in SQL Server 2000, it is new in SQL Server
2005. The trend tends to be that if you don't state what version of SQL
Server it is that you are using that the assumption is made it's SQL Server
2005 as it's the current version. In future it would help if you stated up
front which you are using.

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

>> Table tblTasks contains Auto Incrementing task_ids <<

1) We don't use the "tbl-" prefix in SQL; this is not BASIC. Instead,
name each data element for what it models (see ISO-11179 for rules).
In this case Tasks is fine. Since SQL has only one data structure,
the "tbl_" prefix is really silly.

2) Even Microsoft gave up on camelCase; it is a bitch to read because
your eye is trained to jump to an upper case letter if you work in a
Latin alphabet language.

3) Auto Increments are not identifiers; they record the (attempted)
sequential order of insertions in **physical** storage and are not
part of a valid data model. This will really be a mess in a set-
oriented language like SQL when you have parallel insertions of whole
sets.

What most newbies are doing when they use them as keys is mimicking
the familar pointers or phsycial record numbers they learned in
proceudral languages that use file systems.

Your task_ids are probably something that the accounting department
cares about Why haven't you talked to them and found what the
enterprise uses? If you really have to invent your own, then sit down
and design them properly. Are they ordered in a hierarchy or a
network instead of a pipeline (probably, for any real job)? Do you
want to do PERT or CPM with this data? Have you written constraints
to enforce the proper order of execution of the tasks? Etc.?

>> When the task is completed, a completion_status is set to TRUE. <<

But we don't have Booleans in SQL; there is no TRUE value in the
language. This is not assembly language. SQL is a **predicate**
language in which we discover the state of the data with a predicate
rather than set flags (and hope they match the current state of the
data base). Most likely, you would have a completion_date that has a
start_date to go with it. While the task is open, the completion_date
is NULL. This will give you the duration of the task.

>> 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. <<

We are the database side of the house; we don't care about displaying
in grids when we wear our SQL hat. That is the application side of
the house. What we do is write a query and pass the data over the
wall. Tiered architecture is very different from monolithic languages
which mixed data (files) with application code.

Your specs are a bit vague. In terms of (start_date, completion_date)
pairs, we can see how to find the complete and incomplete tasks. But
how do you want to order them? What are the rules for the same
completion dates? For example:

('2008-07-01', '2008-07-10')
('2008-07-02', '2008-07-10')

What about ties? Etc.?

This is TOTALLY different from application development. It is pure
specification, and declarative logic. No display. No procedural
coding concepts.
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

On Tue, 29 Jul 2008 09:34:01 -0700, TiVo wrote:

>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.

Hi TiVo,

Don't be bothered too much by Celko. Half his posts are partially or
complete rubbish. The other half contain some very valuable advice,
buried under a layer of abusiveness.

However, his point about the design IS actually correct. But from your
description above, I gather that there is actually only one table, but
that you use two queries to get the completed and the incompleted tasks,
right?

An auto incrementing ID should only be used as a surrogate key. This
means that it's a meaningless identifier, used as a surrogate to point
to rows in the table, whereas the "real" key is also kept in the table
(but not used for refering). For instance, some company might identify
me by my full name, plus my address (for there may be more than one Hugo
Kornelis in the world). It's not practical to use name and address in
all tables that refer to customers (orders, payments, etc), so they
assign me some numeric ID, and use that in the other tables.

Since a surrogate key is meaningless, it should not be used for sorting.
After all, that'd mean that you sort on a meaningless thing! Instead,
you sort on real attributes of the things you display. For your list,
the sorting would be: first unfinished task, ordered by ascending
StartDate; then finished task, ordered by descending FinishDate. And
since you'll have to record FinishDate to do this, you now no longer
need the boolean CompletedStatus anymore

SELECT Some, Columns
FROM YourTable
ORDER BY CASE WHEN FinishDate IS NULL THEN StartDate END ASC,
FinishDate DESC;

(Or, if you want to get really tricky, you can use the one below. Not
really good for maintainability, so better not to use in real code, but
nice for entertainment value)

ORDER BY COALESCE(DATEDIFF(dd, FinishDate, '25000101'),
DATEDIFF(dd, '25000101', StartDate);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL 

Your Question:

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

Answer:

(select CategoryId,Name,(Name)as N2,('')as N3 from Category where Name IN('Education','Food') ) union
(select CategoryId,Name,('')as N2,(Name)as N3 from Category )order by 3desc,4 desc

> On Tuesday, July 29, 2008 4:32 AM jhusse wrote:

> 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.


>> On Tuesday, July 29, 2008 4:41 AM Daniel Crichton wrote:

>> TiVo wrote on Tue, 29 Jul 2008 01:32:02 -0700:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> 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


>>> On Tuesday, July 29, 2008 4:55 AM Uri Dimant wrote:

>>> 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
>>>


>>>> On Tuesday, July 29, 2008 4:57 AM Uri Dimant wrote:

>>>> Daniel
>>>> Yes (i had the same in my mind), but it does not give the OP desired result.


>>>>> On Tuesday, July 29, 2008 5:00 AM jhusse wrote:

>>>>> 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...


>>>>>> On Tuesday, July 29, 2008 5:05 AM Uri Dimant wrote:

>>>>>> Sorry Daniel, my mistake


>>>>>>> On Tuesday, July 29, 2008 5:39 AM jhusse 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


>>>>>>>> On Tuesday, July 29, 2008 6:34 AM jhusse wrote:

>>>>>>>> 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)


>>>>>>>>> On Tuesday, July 29, 2008 6:36 AM Tibor Karaszi wrote:

>>>>>>>>> The interesting thing is not what you happened to get on the ORacle system it is what Oracle
>>>>>>>>> *documents* that you should have. By definition, a table is not sorted, and a result from a SELECT
>>>>>>>>> statement is a table. I.e., the DBMS is free to return the rows in any order it wants. Of course,
>>>>>>>>> ORDER BY is an exception, but in your case you didn't have an ORDER BY on the *outer* query (the
>>>>>>>>> result from the UNION. As I mentioned, without an ORDER BY, the RDBMS is free to return the orws in
>>>>>>>>> any order it feels like. "Feels like" will in most cases translate to what the optimizer judgest is
>>>>>>>>> least expensive from a performance perspective.
>>>>>>>>>
>>>>>>>>> So, what I'm trying to say is that just because you happened to get that desired order on the Oracle
>>>>>>>>> system *for this particualr query with the state of the table as they are now and the statistics you
>>>>>>>>> have now and the indexes you have now* doesn't guarantee anything. Just a coniencident. Unless
>>>>>>>>> Oracle deviates in some aspect from the relationalmodes and documents somewhere that it preserves
>>>>>>>>> the ordering. But that fact that your current query bahaves like that isn't proof enough. I suggest
>>>>>>>>> you talk to an Oracle expert of consult the documentation, else code the query so it is safe on
>>>>>>>>> Oracle as well.
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Tibor Karaszi, SQL Server MVP
>>>>>>>>> http://www.karaszi.com/sqlserver/default.asp
>>>>>>>>> http://sqlblog.com/blogs/tibor_karaszi
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> "TiVo" wrote in message
>>>>>>>>>


>>>>>>>>>> On Tuesday, July 29, 2008 6:43 AM Erland Sommarskog wrote:

>>>>>>>>>> TiVo (jhussey@fareham.dontneedthisbit.gov.uk) writes:
>>>>>>>>>>
>>>>>>>>>> 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.DeleteThis@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


>>>>>>>>>>> On Tuesday, July 29, 2008 8:36 AM Daniel Crichton wrote:

>>>>>>>>>>> TiVo wrote on Tue, 29 Jul 2008 03:34:06 -0700:
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> While it might work in all the runs you've made, there's no guarantee that
>>>>>>>>>>> it will continue to work as without an ORDER BY on the outer SELECT the SQL
>>>>>>>>>>> engine is free to return the rows in whatever order it wants. Just because
>>>>>>>>>>> it returns in that order today doesn't mean it will tomorrow.
>>>>>>>>>>>
>>>>>>>>>>> Why are you so vehement in ignoring the suggestions provided that will
>>>>>>>>>>> guarantee the sort ordering and instead continue to insist on using T-SQL
>>>>>>>>>>> that could well end up changing it's sort order at some point in the future?
>>>>>>>>>>> Why not just use an order on the outer query and be happy that it will work
>>>>>>>>>>> no matter how the internal queries are handled in the optimiser?
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> Dan


>>>>>>>>>>>> On Tuesday, July 29, 2008 9:35 AM Roy Harvey (SQL Server MVP) wrote:

>>>>>>>>>>>> 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:


>>>>>>>>>>>>> On Tuesday, July 29, 2008 10:43 AM jhusse wrote:

>>>>>>>>>>>>> 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?


>>>>>>>>>>>>>> On Tuesday, July 29, 2008 10:54 AM Roy Harvey (SQL Server MVP) wrote:

>>>>>>>>>>>>>> On Tue, 29 Jul 2008 07:43:02 -0700, TiVo
>>>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>
>>>>>>>>>>>>>> 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


>>>>>>>>>>>>>>> On Tuesday, July 29, 2008 11:15 AM 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 Tuesday, July 29, 2008 11:36 AM jhusse wrote:

>>>>>>>>>>>>>>>> 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:


>>>>>>>>>>>>>>>>> On Tuesday, July 29, 2008 12:23 PM Daniel Crichton wrote:

>>>>>>>>>>>>>>>>> TiVo wrote on Tue, 29 Jul 2008 08:36:06 -0700:
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> This function does not exist in SQL Server 2000, it is new in SQL Server
>>>>>>>>>>>>>>>>> 2005. The trend tends to be that if you don't state what version of SQL
>>>>>>>>>>>>>>>>> Server it is that you are using that the assumption is made it's SQL Server
>>>>>>>>>>>>>>>>> 2005 as it's the current version. In future it would help if you stated up
>>>>>>>>>>>>>>>>> front which you are using.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>> Dan


>>>>>>>>>>>>>>>>>> On Tuesday, July 29, 2008 12:34 PM jhusse wrote:

>>>>>>>>>>>>>>>>>> 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:


>>>>>>>>>>>>>>>>>>> On Tuesday, July 29, 2008 12:34 PM Tom Cooper wrote:

>>>>>>>>>>>>>>>>>>> Glad to hear it.
>>>>>>>>>>>>>>>>>>> It's a nice result in this newsgroup when you not only get the answer to
>>>>>>>>>>>>>>>>>>> your problem, but actually learn how to generate it yourself.
>>>>>>>>>>>>>>>>>>> BTW, I don't think anyone thanked you for providing the DDL and INSERT
>>>>>>>>>>>>>>>>>>> statements to demonstrate your problem in your original post. That is
>>>>>>>>>>>>>>>>>>> always very helpful and we appreciate it. Thank you.
>>>>>>>>>>>>>>>>>>> And now, you've been yelled at by CELKO, that's the ultimate validation as a
>>>>>>>>>>>>>>>>>>> "real-world" SQL programmer. Congradulations.
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> Tom
>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>> "TiVo" wrote in message
>>>>>>>>>>>>>>>>>>>


>>>>>>>>>>>>>>>>>>>> On Tuesday, July 29, 2008 12:57 PM jhusse wrote:

>>>>>>>>>>>>>>>>>>>> Thanks Uri.
>>>>>>>>>>>>>>>>>>>> Unfortunately I forgot to mention that I have to program against SQL Server
>>>>>>>>>>>>>>>>>>>> 200 SP4 which doesn't have ROW_NUMBER().
>>>>>>>>>>>>>>>>>>>> Still, It's a good trick to remember once we upgrade to 2007 in the near
>>>>>>>>>>>>>>>>>>>> future.
>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>> "Uri Dimant" wrote:


>>>>>>>>>>>>>>>>>>>>> On Tuesday, July 29, 2008 3:58 PM Roy Harvey (SQL Server MVP) wrote:

>>>>>>>>>>>>>>>>>>>>> On Tue, 29 Jul 2008 11:15:03 -0400, "Tom Cooper"
>>>>>>>>>>>>>>>>>>>>> wrote:
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> The odd/even test nonsense in my example was simply part of pretending
>>>>>>>>>>>>>>>>>>>>> to have a table with the attributes of the test data posted. I didn't
>>>>>>>>>>>>>>>>>>>>> intend the modulo test to be interpreted as part of the solution. I
>>>>>>>>>>>>>>>>>>>>> guess I should have made that clear.
>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>> Roy Harvey
>>>>>>>>>>>>>>>>>>>>> Beacon Falls, CT


>>>>>>>>>>>>>>>>>>>>>> On Tuesday, July 29, 2008 4:23 PM jhusse wrote:

>>>>>>>>>>>>>>>>>>>>>> That's the trouble with this kind of interaction.
>>>>>>>>>>>>>>>>>>>>>> No wonder projects fail when they have outsourcing on different continents.
>>>>>>>>>>>>>>>>>>>>>> A good 10 minute chat would have worked wonders!
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> Thanks for your help
>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>> "Roy Harvey (SQL Server MVP)" wrote:


>>>>>>>>>>>>>>>>>>>>>>> On Wednesday, July 30, 2008 4:19 PM Hugo Kornelis wrote:

>>>>>>>>>>>>>>>>>>>>>>> On Tue, 29 Jul 2008 09:34:01 -0700, TiVo wrote:
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> Hi TiVo,
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> Don't be bothered too much by Celko. Half his posts are partially or
>>>>>>>>>>>>>>>>>>>>>>> complete rubbish. The other half contain some very valuable advice,
>>>>>>>>>>>>>>>>>>>>>>> buried under a layer of abusiveness.
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> However, his point about the design IS actually correct. But from your
>>>>>>>>>>>>>>>>>>>>>>> description above, I gather that there is actually only one table, but
>>>>>>>>>>>>>>>>>>>>>>> that you use two queries to get the completed and the incompleted tasks,
>>>>>>>>>>>>>>>>>>>>>>> right?
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> An auto incrementing ID should only be used as a surrogate key. This
>>>>>>>>>>>>>>>>>>>>>>> means that it's a meaningless identifier, used as a surrogate to point
>>>>>>>>>>>>>>>>>>>>>>> to rows in the table, whereas the "real" key is also kept in the table
>>>>>>>>>>>>>>>>>>>>>>> (but not used for refering). For instance, some company might identify
>>>>>>>>>>>>>>>>>>>>>>> me by my full name, plus my address (for there may be more than one Hugo
>>>>>>>>>>>>>>>>>>>>>>> Kornelis in the world). It's not practical to use name and address in
>>>>>>>>>>>>>>>>>>>>>>> all tables that refer to customers (orders, payments, etc), so they
>>>>>>>>>>>>>>>>>>>>>>> assign me some numeric ID, and use that in the other tables.
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> Since a surrogate key is meaningless, it should not be used for sorting.
>>>>>>>>>>>>>>>>>>>>>>> After all, that'd mean that you sort on a meaningless thing! Instead,
>>>>>>>>>>>>>>>>>>>>>>> you sort on real attributes of the things you display. For your list,
>>>>>>>>>>>>>>>>>>>>>>> the sorting would be: first unfinished task, ordered by ascending
>>>>>>>>>>>>>>>>>>>>>>> StartDate; then finished task, ordered by descending FinishDate. And
>>>>>>>>>>>>>>>>>>>>>>> since you'll have to record FinishDate to do this, you now no longer
>>>>>>>>>>>>>>>>>>>>>>> need the boolean CompletedStatus anymore
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> SELECT Some, Columns
>>>>>>>>>>>>>>>>>>>>>>> FROM YourTable
>>>>>>>>>>>>>>>>>>>>>>> ORDER BY CASE WHEN FinishDate IS NULL THEN StartDate END ASC,
>>>>>>>>>>>>>>>>>>>>>>> FinishDate DESC;
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> (Or, if you want to get really tricky, you can use the one below. Not
>>>>>>>>>>>>>>>>>>>>>>> really good for maintainability, so better not to use in real code, but
>>>>>>>>>>>>>>>>>>>>>>> nice for entertainment value)
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> ORDER BY COALESCE(DATEDIFF(dd, FinishDate, '25000101'),
>>>>>>>>>>>>>>>>>>>>>>> DATEDIFF(dd, '25000101', StartDate);
>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>>>>>> Hugo Kornelis, SQL Server MVP
>>>>>>>>>>>>>>>>>>>>>>> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis


>>>>>>>>>>>>>>>>>>>>>>>> On Thursday, July 31, 2008 11:55 PM --CELKO-- wrote:

>>>>>>>>>>>>>>>>>>>>>>>> One way to produce this result in any SQL, including Oracle and SQL
>>>>>>>>>>>>>>>>>>>>>>>> Server, is to write the two outputs to a sequential file. No SQL
>>>>>>>>>>>>>>>>>>>>>>>> guarantees the order of a result without an ORDER BY clause. Most of
>>>>>>>>>>>>>>>>>>>>>>>> them will catch that silly use of an ORDER BY in derived tables and
>>>>>>>>>>>>>>>>>>>>>>>> flag it as an error (ORDER BY can only appear on a cursor in Standard
>>>>>>>>>>>>>>>>>>>>>>>> SQL), give a warning or ignore it.
>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>> Please get a book on RDBMS and read the first 2-3 chapters about why a
>>>>>>>>>>>>>>>>>>>>>>>> table is not a file, a column is not field and a row is not a record.


>>>>>>>>>>>>>>>>>>>>>>>>> On Thursday, July 31, 2008 11:56 PM --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.


>>>>>>>>>>>>>>>>>>>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>>>>>>>>>>>>>>>>>>>> Scrolling in WPF Toolkit?s Column Chart
>>>>>>>>>>>>>>>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/0939d60c-8e17-4a27-b898-1f...2d2d6f6
 >> Stay informed about: Problem With ORDER BY DESC and UNION ALL