Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Question Unanswered: 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 subquery
    Code:
    select 
    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...
    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 desc
    but 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!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    EDIT: Oops - retracted.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    dang, and I had my blistering rebuttal all ready
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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?
    Last edited by TallCowboy0614; 03-01-06 at 18:57.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Oops...sorry.

    Quote Originally Posted by SQL Server Compiler
    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 )

    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's start by replacing that hideous CASE expression

    Code:
    select upper(left(datename(dw,getdate())
                   ,3+datepart(dw,getdate())%2)) as DOW
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    the whole UNION is one result set and the order by applies to the whole result set.
    Last edited by Thrasymachus; 03-02-06 at 20:48.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here, this works:
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    he already had one that works. he was just wondering why the other did not. but yes yours is prettier.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •