using 'order by' to order data from two text columns

[theanteater]

Hi all,

 

I have been trawling through forum posts and mysql tutorials for hours but cant find a soultion to this. I'm sure it must have been asked a hundred times before:

 

I have a table with two name columns. I need to combine these two colomns and order alphabetically from the result?

 

SELECT * FROM table WHERE a='$b' etc ORDER BY (name_col_1 AND name_col_2) // this doesnt work

SELECT * FROM table WHERE a='$b' etc ORDER BY (name_col_1 + name_col_2) // this doesnt work

SELECT * FROM table WHERE a='$b' etc ORDER BY (name_col_1 || name_col_2) // this doesnt work

 

I have tried using UNION and JOIN and CONCAT but cant seem to get it. Any help would be great?

 

Thanks, Nigel

[DavidAM]

You just separate them with a comma

ORDER BY name_col1, name_col2

[PFMaBiSmAd]

ORDER BY name_col_1, name_col_2 (will sort by col_1 ASC, then col_2 ASC)

 

ORDER BY name_col_2, name_col_1 (will sort by col_2 ASC, then col_1 ASC)

 

ORDER BY name_col_1 DESC, name_col_2 (will sort by col_1 DESC, then col_2 ASC)

 

ORDER BY name_col_1 , name_col_2 DESC (will sort by col_1 ASC, then col_2 DESC)

 

ORDER BY name_col_1 DESC, name_col_2 DESC (will sort by col_1 DESC, then col_2 DESC)

[theanteater]

Yes, thanks. I tried those but they all give proirity to the first argument.

 

I need to take the data from both columns and sort it with equal priority.

 

eg.

 

name_col1          name_col2

andrew              brian

james                  colin

fred                    david

 

Result = andrew,brian,colin,david,fred,james

 

Thanks

 

[DavidAM]

If it is just the names you want, I think this would work:

SELECT name_col1 AS name_col FROM table WHERE a=$b
UNION 
SELECT name_col2 AS name_col FROM table WHERE a=$b
ORDER BY 1 

 

You will not know which is which. Of course you could add a couple of fields:

 

SELECT name_col1 AS name_col, id, 'col1' AS which_name FROM table WHERE a=$b
UNION 
SELECT name_col2 AS name_col, id, 'col2' AS which_name FROM table WHERE a=$b
ORDER BY 1 -- Orders by the first column

 

[theanteater]

Thanks David but that doesn't seem to be working. Producing two of all the entries and sorting them again by either the first or second select.

 

How about taking the data from the two fields. Using that data to make a temp table with only one name field and then ordering by that field? Echoing the results then deleting the table. Is that doable?

 

Here is the actual code in it's current 'not working' form if it helps?

 

$qry = "SELECT emailto AS name_col,date,emailto,emailfrom FROM messages WHERE (emailto='$userid' AND to_deleted='Yes' AND to_destroyed='No') OR  (emailfrom='$userid' AND from_deleted='Yes' AND from_destroyed='No') UNION SELECT emailfrom AS name_col,date,emailto,emailfrom FROM messages WHERE (emailto='$userid' AND to_deleted='Yes' AND to_destroyed='No') OR  (emailfrom='$userid' AND from_deleted='Yes' AND from_destroyed='No') ORDER BY name_col LIMIT $nextlimit,$users_per_page";

 

$qrys = mysql_query($qry)or die ("Error #2 Finding Emails: ". mysql_error());

[theanteater]

This is close but not working...produces the results mixed up. Not ordered by name??

 

$qry = "SELECT emailto AS name_col,date,id,emailto,emailfrom FROM messages WHERE (emailto='$userid' AND to_deleted='Yes' AND to_destroyed='No') UNION SELECT emailfrom AS name_col,date,id,emailto,emailfrom FROM messages WHERE (emailfrom='$userid' AND from_deleted='Yes' AND from_destroyed='No') ORDER BY name_col DESC LIMIT $nextlimit,$users_per_page";

 

$qrys = mysql_query($qry)or die ("Error #2 Finding Emails: ". mysql_error());

[fenway]

Please post actual queries.