Order By date Desc issue

I have these queries:

(SELECT * FROM product WHERE var<>'0'ORDER BY date DESC) 

and

(SELECT * FROM product WHERE var='0' ORDER BY date DESC).

When i try to unite the result from the two into one with

 (SELECT * FROM product WHERE var<>'0'ORDER BY date DESC) 
 UNION 
 (SELECT * FROM product WHERE var='0' ORDER BY date DESC)

The result lose the order by... the resulting set has first all the records with var not zero and then all the record with var zero but them are not ordered by date... Singularly the order is working... Why? I tried to use a unique order by but the result mix the record having var equal or not to zero...

Answers


I believe what you're trying to do is order the result set putting '0' last, then order each subset by date. In that case you can forget the union and do this instead:

SELECT *
FROM product
WHERE var IS NOT NULL
ORDER BY CASE WHEN var = '0' THEN 2 ELSE 1 END, date DESC

You have to apply the order last:

SELECT * FROM product WHERE var<>'0'
UNION 
SELECT * FROM product WHERE var='0'
ORDER BY date DESC

Edit if you instead want to preserver the order in both parts, so first all with var<>'0' (internally ordered by date DESC) and then all with var='0' (internally ordered by date DESC):

SELECT * FROM product WHERE var<>'0'
UNION 
SELECT * FROM product WHERE var='0'
ORDER BY 
    CASE WHEN var<>'0' THEN 0 ELSE 1 END ASC,
    date DESC

SELECT * FROM product WHERE var<>'0'
UNION 
SELECT * FROM product WHERE var='0'
ORDER BY date DESC

is equivalent to:

SELECT * FROM product
ORDER BY date DESC

Need Your Help

Standard Way Of Verifying Uploaded File Types

.net wcf c#-4.0

I am exposing a WCF service which allows a third party to upload files. It has been open until now as to what files you can upload as this is the first phase, but for phase two I might have to incr...

Update template inside a view rendered from another controller

ajax grails render gsp grails-controller

I am looking for the way to refresh a template inside a view rendered from another controller than the template's controller, I mean:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.