Union Query Results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Union Query Results

Post by JakeJ »

Great, my previous question was answered, but I'm getting some unexpected results.

(SELECT * FROM table WHERE type_id !=1 order by nper ASC)
UNION
(SELECT * FROM table WHERE type_id =1 ORDER BY nper ASC)

Either way I get the same results which appears that nper isn't getting sorted at all.

here's my results

nper type_id
130 3
138 3
48 2
32 2
142 3
129 1
119 1

So, my type_id = 1 is showing up at the bottom as expected but it's not sorting properly. I've left off the sorts, I tried DESC just for kicks. It's all the same.

In the mean time, I've created another field called priority and the values are either 1 or 0. So my query now looks like this:

SELECT * FROM table ORDER BY priority ASC nper ASC. I get the results I expect now but it shouldn't require an extra field to do so.
Brian Swan
Forum Newbie
Posts: 17
Joined: Thu Jan 14, 2010 11:56 am

Re: Union Query Results

Post by Brian Swan »

Have you tried this...

SELECT * FROM table WHERE type_id !=1
UNION
SELECT * FROM table WHERE type_id = 1
ORDER BY nper ASC

According to the documentation for UNION (http://msdn.microsoft.com/en-us/library/ms180026.aspx), you have to apply the ORDER BY clause after the UNION.

Hope that helps.

-Brian
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Union Query Results

Post by AbraCadaver »

Brian Swan wrote:Have you tried this...

SELECT * FROM table WHERE type_id !=1
UNION
SELECT * FROM table WHERE type_id = 1
ORDER BY nper ASC

According to the documentation for UNION (http://msdn.microsoft.com/en-us/library/ms180026.aspx), you have to apply the ORDER BY clause after the UNION.

Hope that helps.

-Brian
Actually, you only apply the ORDER BY after the UNION if you want all of your results from both tables returned and then ordered. Based on JakeJ's other post, he wanted all the type_id != 1 at the beginning of the results sorted by nper and all the type_id = 1 at the end of the results sorted by nper. I'm not sure why it's not working.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Union Query Results

Post by JakeJ »

AbraCadaver is right, I need all type_id != 1 at the beginning and type_id= 1 at the end.

But the query you suggested sorts everything by nper which isn't what I need. And I'm still perplexed on why my UNION query doesn't work properly.

I will just stick with the field I added, that seems to be working well enough. Thanks for trying guys.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Union Query Results

Post by VladSun »

Read this:
http://dev.mysql.com/doc/refman/5.0/en/union.html
...However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway....
To cause rows in a UNION result to consist of the sets of rows retrieved by each SELECT one after the other, select an additional column in each SELECT to use as a sort column and add an ORDER BY following the last SELECT:

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;
There are 10 types of people in this world, those who understand binary and those who don't
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

SOLVED: Union Query Results

Post by JakeJ »

THAT EXPLAINS IT! Thanks!
Post Reply