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.
Union Query Results
Moderator: General Moderators
-
Brian Swan
- Forum Newbie
- Posts: 17
- Joined: Thu Jan 14, 2010 11:56 am
Re: Union Query Results
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
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
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Union Query Results
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.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
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.
Re: Union Query Results
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.
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.
Re: Union Query Results
Read this:
http://dev.mysql.com/doc/refman/5.0/en/union.html
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
SOLVED: Union Query Results
THAT EXPLAINS IT! Thanks!