ORDER BY in subquery of a UNION fails ??

Hi all,

I have the following UNION ALL statement that is my attempt to gather data for the past 5 weekdays (adding a "dummy" row for today's data).

I want the final output to end up in descending order, so for today, I would want today first, then Tuesday, then Monday, then Friday, then Thursday (provided there is data for each sequential day - if not, you get the idea, I want to select back to get the latest 5 days, most recent to oldest).

This select fails, because it doesn't like the ORDER BY in the subqueryselect
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight
UNION ALL
(select top 4
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc )

I know you can't use an ORDER BY in a subquery, UNLESS the subquery also uses a TOP n (which this one does)...but does anyone know why this isn't liking my code?

I got the select to work the way I want it to by doing the following (really UGLY) code...SELECT U.DOW, U.Freight FROM
((select
GETDATE() as [OrderDate],
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight )
UNION ALL
(select h.OrderDate as [OrderDate], h.DOW, h.Freight FROM
(select top 4
[OrderDate] as [OrderDate],
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc ) H)) U
order by OrderDate descbut am still confounded about why my original sub-select is rejected with such impunity.

My confusion seems likely related to understanding the set theory or basic concepts of the building of the select/Union rather than the way I am using the ORDER BY syntax, but I just can't seem to explain it to myself.

Thoughts?
Thanks!EDIT: Oops - retracted.|||dang, and I had my blistering rebuttal all ready ;)|||Damn that sucks. I was about to post that your place holder does not have an orderdate but neither does the one that works. Going to have to look at this when I get home.|||OMG NOOOOOO!!! Not working on SQL at HOME!! I wouldn't want to be responsible for THAT in any way, shape or form!

Home is for Beer, relaxation, Lovin', and...well...pretty much ANYTHING but working on Paul's SQL questions ;)

...and what do you mean by "placeholder", BTW?|||please explain what "isn't liking my code" means

is there an error message?

without it, we're just guessing

there are other ways of getting the last 4 order dates, by the way, but they involve a self-join or yet another subselect|||Server: Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'order'.Yeah, This is not probably the way I woulda done it...

Had a peer at work come to me with this, and ask me why the order-by in the sub-query doesn't work. I could not explain it, so I just banged my head against it until I figured out a way. Then posted here my ugly code just to show what worked. And so anyone so motivated could run the select to see what I am after for output.

Of course a coupla decades in software development have taught me that you are indeed right...more'n one way to skin a catfish. I am of course open to other alternatives, though am not sure off the top of my head that a self-join would be any less ugly (especially when this sub-select theoretically SHOULD work). Of course, I am always learning, and not typically arrogant enough to think my way is the only way :) (though mind you , I am in no way adverse to steppin up to arm-wrestle about it being the RIGHT way :D )

If you want to take the time to post other ways or improvements, I am ALWAYS open to such nudges or downright slapdowns - in fact I LOVE 'em.

I always appreciate it when someone sees thier way clear to burn a few brain cells on my behalf.|||let's start by replacing that hideous CASE expression

select upper(left(datename(dw,getdate())
,3+datepart(dw,getdate())%2)) as DOW|||i'm sorry, i just discovered i don't have northwind to play with

i'll see if i can squeeze this in tomorrow at the job|||since she's been gone it's all work,exercise study sleep. occasionally I go out with the boys, but my first instinct was right.

in the first one that does not work there is no order date in the first half of your union and in the second one you alias GETDATE() as your order date. my brain was just too mushy earlier.|||Hmmmm...if I understand you correctly, I think that is my intent (which doesn't mean SQL will let me do what I think I want to *L*)

I am only trying to ORDER BY the second select in the UNION...that is because the table used in the second union has daily data in it since about June of 1963 (aka, many more than the 4 dates I am interested in). So the second part of the union is simply trying to grab the last 4 dates (most recent) which is why I am trying to use the TOP 4...ORDER BY - - to get just the last 4 dates in table).

My intent in the first (non-working) UNION is to put my "hard-coded" row of TODAY's data in, then add to it the latest 4 days from the table in the second select of the UNION.

I also want the whole thing ordered by date, which is missing from the first UNION, but that part shouldn't matter to me, should it? At least relative to the pulling of the 5 rows I want. The second (working, ugly) select DOES have the overall ORDER BY in it, which probably causes some confusion relative to my question.

I am just trying to figure out why the initial (non-working) select complains of a syntax error, when to my way of thinking, I should be able to put an idependent select in the second half of the UNION and apply the ORDER BY only to that sub-select (it would, again, be a seperate issue to order the whole result of the UNION).

In a nutshell, if I run the second select in the first UNION by itself, it works fine. But when I try to run the whole UNION select, it fails with the previously-noted error. Shouldn't my sub-select work within the UNION if I place it inside parenthesis as I have?

Part of the problem may be that I am not doing a good job of explaining specifically what I think should work *LOL* - - or not understanding your explaination of why it WON'T.

As always, thanks for expending brain cell activity on my behalf.|||the whole UNION is one result set and the order by applies to the whole result set.|||here, this works:select *
from (
select top 4
[OrderDate]
, substring('SUN MON TUESWED THURFRI SAT '
, 4*datepart(dw,[OrderDate])-3,4) as DOW
, right(space(10)+CAST(Freight*100 as VARCHAR(10)),10) as Freight
from Northwind.dbo.orders
where employeeid = 9
order by [OrderDate] desc
) as dt
union all
select getdate()
, substring('SUN MON TUESWED THURFRI SAT '
, 4*datepart(dw,getdate())-3,4) as DOW
, ' N/A' AS Freight|||he already had one that works. he was just wondering why the other did not. but yes yours is prettier.|||AH-HAA!!!!!

I KNEW it was a syntax assumption...

FROM BOL:
The ORDER BY clause can include items not appearing in the select list. However, if SELECT DISTINCT is specified, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

MY problem was that I was trying to FORCE the ORDER BY in my sub-query to work like a NON-UNION sub-query, and could not figure out why my sub-query (with order by) would not function as an independent sub-query (as it would in a JOIN, for example).

And you guys, on a different plane *L*, assumed that I was playing by the taken-for-granted UNION rules understanding.

I KNEW it was gonna be something like that.

Sincere thanks for your patience and willingness to work with me on this. When I cannot understand why something doesn't work when I am playing by the rules, it often turns out that I am not playing by the rules ;)

...either that, or God is just toying with me at the time...

In this case it was the former.

Thanks for trying to explain when I refused to listen - y'all are now in the same fine group as my parents, teachers, and...well, pretty much everyone else I know :D|||in the first one that does not work there is no order date in the first half of your union and in the second one you alias GETDATE() as your order date. my brain was just too mushy earlier.

this is what i was saying, right?|||Yeah, that's what I mean though...I was thinking differently, so even though you posted that, I thought "huh? I don't WANT a date in the first part of the UNION", because I didn't need it in the final output.

At that time I didn't yet realize/know that it was REQUIRED to make the UNION work.

Theoretically, I shouldn't NEED it (SQL Server notwithstanding) because the output of various selects in the UNION should (logically, or non-logically ;) ) be independent of each other, right? From a set theory-type perspective, anyway.

My second select (the one that worked) only ACCIDENTALLY unbroke the rules that got me in the first one.

Thanks regardless, Sean...I really shouldn't let anyone know any more about my thought process than I have to ;)