ok i don't want something like this

select * from table where feild1=1 & field2=2 order by field1 and field2

but i want something like this

select * from table where feild1=1 order by field1 desc

and then i want to show the remaining records ordered by field2

do i have to do 2 sql qureies or is there another way?

I think I know what you're asking. If I've got it wrong then tell me.


SELECT * FROM table WHERE field1 = 1 ORDER BY field1 DESC
UNION ALL
SELECT * FROM table WHERE field1 != 1 ORDER BY field2 DESC


That's one query. UNION ALL instead of just UNION ensures that duplicates will remain. However, based on the logic of the query it shouldn't be possible for duplicates to exist. You can remove the 'ALL' if you choose in that case.

I made an error in the select. you'll have to surround the queries in (). I'm also assuming that you're using Mysql4.0 or higher


(SELECT * FROM table WHERE field1 = 1 ORDER BY field1 DESC)
UNION ALL
(SELECT * FROM table WHERE field1 != 1 ORDER BY field2 DESC)

thanks yeah that what i'm after

so will all the records from this query be in 1 result set?

like i'll use

do while result <0

echo results

and that will display in the order i want.

Yes, the records will be in one result set and when you retrieve the info from the database they should be in order.

Code wise, I'm only familiar with accessing mysql via PHP. The snippet you posted doesn't make use of PHP's mysql functions such as mysql_fetch_array etc so I assume this is not what you're using.

Regardless of that fact the results should be the same, a manual check of the the results should still be done at least once to make sure that it is behaving correctly.

i am using mysql_fetch_array, lol my examples be realy rough because i can't code much from scrach. i use a basic example and change it to what i need it to do.

hi seiken
can you please help again.


$sql=('SELECT * FROM table WHERE f1!=0 ORDER BY c DESC')
UNION All
('SELECT * FROM table WHERE f1=0 ORDER BY d DESC');

i'm trying to run this query and load the results into $result = mysql_query($sql);


but its not working . how would i do that?

$sql variable is a STRING:

$sql="(SELECT * FROM table WHERE f1!=0 ORDER BY c DESC) UNION ALL (SELECT * FROM table WHERE f1=0 ORDER BY d DESC)";

This can help:

http://dev.mysql.com/doc/mysql/en/union.html

thanks berumelu, I'll give it a try.