SQL UNION and ORDER BY

Select id,Name,DayLeft
from
(Select Top 100 Id,Name,DayLeft from table_1 WHERE DayLeft > 0 ORDER BY DayLeft , Name)
AS T1
Union All
Select id,Name,DayLeft
from
(Select Top 100 Id,cName,DayLeft from table_1 WHERE DayLeft <= 0 ORDER BY Name )
AS T2

I'm affraid that didn't work for me angel, gave me an error:
   " Syntax error (missing operator) in query expression 'CASE WHEN DaysLeft > 0 THEN 0 ELSE 1 END'. "

Any idea there?
I looked into ORDER BY CASE after your post and the follow works, to a point, for me:
   ORDER BY IIF(DaysLeft > 0, DaysLeft, Name)

But I couldn't find a way to then order by multiple fields i.e.
   ORDER BY IIF(DaysLeft > 0, (DaysLeft, Name), Name)

Gave a syntax error:
   " Syntax error (comma) in query expression

Any ideas either way?

Try this one. I think this rewrite will work

SELECT ID, [Name], DaysLeft
FROM  dbo.MyTable
ORDER BY 
     CASE 
          WHEN DaysLeft >= 0 THEN 0
          ELSE 1
     END, ABS(DaysLeft), Name

                                              

1:
2:
3:
4:
5:
6:
7:

Try this for Oracle

SELECT 'A'||rownum,name,DAYSLEFT FROM (
SELECT * FROM MyTable
WHERE DaysLeft > 0 ORDER BY DaysLeft ASC, Name ASC
)
UNION ALL
select 'B'||rownum,name,DAYSLEFT from(
SELECT * FROM MyTable  
WHERE DaysLeft <= 0 ORDER BY Name ASC)
order by 1

SELECT * FROM YOURTABLE
ORDER BY CASE WHEN DaysLeft > 0 THEN DaysLeft||Name ELSE Name END

TRY THIS

oups, overlooked the "access" part.
so, indeed, iff() instead of the CASE ...

I would go with Angel's answer with IIF.  Limbeck's is close, but forgot the Abs() portion.

So this is Angel's solution:
(please credit post -- http:#25140983)
ORDER BY CASE WHEN DaysLeft > 0 THEN 0 ELSE 1 END, ABS(DaysLeft), Name

In MS Access:
ORDER BY IIF(DaysLeft > 0, 0, 1), ABS(DaysLeft), Name

Regards,
Kevin

how about

ORDER BY CASE WHEN DaysLeft > 0 THEN Daysleft ELSE 999999 END,  Name  

:)

And to be clear, you don't need UNION with Angel's approach.  And looking at your requirements again, you don't seem to want the records ordered by DaysLeft when less than zero so Limbeck may have been closer than I thought. :)

However, try this:
(Use a really high number to push the negatives together)

SELECT * 
FROM MyTable
ORDER BY IIF(DaysLeft > 0, DaysLeft, 999999999), Name

                                              

1:
2:
3:

LOL, Ed.  Guess we had the same thought.  Don't forget this is still Access. :)

heyyyyy lol now i forgot the iif pfft serves me right for doing this while working

I am working with ASP, Access unfortunatly, so I believe "CASE WHEN" is out of the question.

As for IIF, as I said, I took the idea from Angel's post to create "IIF(DaysLeft > 0, DaysLeft, Name)" which worked, but not for subordering.

"ORDER BY IIF(DaysLeft > 0, 0, 1), ABS(DaysLeft), Name" gives the same issue above - only orders by one field, either DaysLeft or Name, when really I would like it to be (DaysLeft, Name) or (Name).
I don't understand how ORDER BY IIF(DaysLeft > 0, 0, 1), ABS(DaysLeft), Name works, so I can't adapt it.

Ideas?

Try my solution posted here -- http:#25141597.  Limbeck's post is similar just with CASE statement, but same solution.

The reason "IIF(DaysLeft > 0, DaysLeft, Name)" didn't work is that DaysLeft is numerical field and Name is not, therefore, you are ordering by two different data types in the same column.  You will have to take Angel's approach of having a sub sort but using the first column to push all the zero or less values to bottom.  At least that was the intent of the 0 or 1.  

My suggestion takes it the step further and uses DaysLeft and then a large number to push those records to bottom.  Then you order by Name as secondary sort and all should be well.

Regards,
Kevin

>I don't understand how ORDER BY IIF(DaysLeft > 0, 0, 1), ABS(DaysLeft), Name works, so I can't adapt it.
simple:
first order is by either 0 ( if the daysleft > 0 ) or 1 (daysleft <= 0 )... means the rows with daysleft > 0 should come first
then, it shall order by the Daysleft value , finally by the name.
but, I see we need to modify the second order...

ORDER BY IIF(DaysLeft > 0, 0, 1), IIF(DaysLeft > 0, DaysLeft, 0), Name 

                                              

1:

Angel, you are reversing the order :) the negative days will preceed the positive days

imho opinion Kevin''s answer is the only correct one.

Ed.

Both of the following code snippets yield the same result for me, which is to order all positive DaysLeft by DaysLeft, and order all others by Name.
It doesn't seem to order all positives by DaysLeft THEN name, and others by Name, which would be great.

scratch opinion

For got to attached snippets ...

ORDER BY IIF(DaysLeft > 0, 0, 1), ABS(DaysLeft), Name
 
ORDER BY IIF(DaysLeft > 0, DaysLeft, 999999999), Name

                                              

1:
2:
3:

im not following you,can you explain with a few rows of data of what goes wrong and what the desired output is?

Just tried your new one Angel
  "ORDER BY IIF(DaysLeft > 0, 0, 1), IIF(DaysLeft > 0, DaysLeft, 0), Name"
and it gave me the same as the previous.

Limbeck, what I'm after is
All positives, ordered by DaysLeft, then ordered by Name when DaysLeft are equal.
Then all zeros and negatives, ordered only by Name.

Thank you for the code explainations, I appreciate them.

I can now see how "IIF(DaysLeft > 0, 0, 1), DaysLeft, Name" would work exactly how I'd want it, but now do not udnerstand why it does not, the results I am getting are:
All positives, ordered by Daysleft,
Then all zeros and negatives, ordered only by Name

As you can see, it seems to be ignoring the command to order secondarily by Name when the DaysLeft are positive, can you sere any reason why it may be doing this?

nope Kevin, angels post is wrong :) all the negative days get a value=0, which will make them appear before the names with a positive day :)

andoneknight, of the suggestions mentioned above only

ORDER BY IIF(DaysLeft > 0, DaysLeft, 999999999), Name   (copied from mwvisa1)

should give you the desired result

Oh sweet mother of ... Sorry guys, it is working, perfectly.
I was misreading the data output in my system - people have double-barreled surnames and forenames confused me as to how the ordering should look.

Thank you all, I will assign points now.

Glad to be of help,
Ed

I think you accepted the wrong answer though :) , accepted answer should be id 25141597

Final query ordering was as follows in the snippet.

Apologies for not realising my error eariler.
Thanks again.

ORDER BY IIF(DaysLeft > 0, 0, 1), DaysLeft, Name

                                              

1:

andoneknight, play around with your data, you will see that that wont work :)

try errr

Mark -5 days
Edwin -2 days
Zack -1 day

just for fun :)

Thank you Limebeck, I appreciate your final words!
Query has been changed to match Angel's final post, see snippet.

SELECT * FROM peeps ORDER BY IIF(DaysLeft> 0, 0, 1), IIF(DaysLeft > 0, DaysLeft, 0),  Name

                                              

1: