Page 1 of 2

MySQL 'ORDER BY' from 2 columns but in alp order over both

Posted: Tue Jan 24, 2006 12:16 pm
by mikebr
I am trying to return column values in alphabetical order over two columns...

Code: Select all

$query = @mysql_query("SELECT `cid`, `company`, `name`, `town` FROM `clients` ORDER BY `country`, `name`");
Thing is the country column is returned in order then the name column after the company column values, is there a way of getting the columns in order together, eg.

Above query returns:

Alpha (Company)
Anglerson (Company)
Bost (Company)
Tudor (Company)
Anderson (Name)
Boston (Name)
Jones (Name)

I am trying to get:

Alpha (Company)
Anderson (Name)
Anglerson (Company)
Bost (Company)
Boston (Name)
Jones (Name)
Tudor (Company)

Thanks for any suggestions

Posted: Tue Jan 24, 2006 12:20 pm
by josh
You'd have to union it together or something? Don't really get what you're asking

Code: Select all

(
select `company` as a, '(company)' from table
)
union
(
select `name` as a, '(name)' from table
)
order by a
You can only order on one column at a time, the next order by part only takes effect when your first column has duplicate values

Posted: Tue Jan 24, 2006 4:08 pm
by mikebr
Sorry, I mixed 'company' with 'country' in the example query, it should have been...

Code: Select all

$query = @mysql_query("SELECT `cid`, `company`, `name`, `town` FROM `clients` ORDER BY `company`, `name`");
Basically I want to return 'by alphabetal order' the text values from two columns within in the same table, so the results would read:

Entries starting with A from both company and name columns
Entries starting with B from both company and name columns
Entries starting with C from both company and name columns
Entries starting with D from both company and name columns
Entries starting with E from both company and name columns
Entries starting with F from both company and name columns
etc.

and not:

Entries starting with A from company column
Entries starting with B from company column
Entries starting with C from company column
etc.
Entries starting with A from name column
Entries starting with B from name column
Entries starting with C from name column
Entries starting with D from name column
etc.

Thanks

Posted: Tue Jan 24, 2006 4:20 pm
by RobertGonzalez
I don't think you are going to get what you want from a regular straight select query. What you are trying to do is take the returned values from two different columns and make them one column. I can't think of a way to make MySQL select two different columns into one result set.

If you are utilizing this query with a server-side script you can always use your code to match the array the way you want. Select company and read it into an array. Then select name and append that into the company array, then sort the array based on its key (which will be the combination of the company and name arrays).

Posted: Tue Jan 24, 2006 4:48 pm
by raghavan20

Code: Select all

mysql> select * from test1;
+-----------+------+
| company   | name |
+-----------+------+
| google    | gg   |
| yahoo     | yh   |
| microsoft | msft |
+-----------+------+
3 rows in set (0.01 sec)

mysql> create view unitedview as
    -> select company from test1
    -> union
    -> select name from test1
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> show create view unitedview;
---------------------------------------------------------------------------------------------+
| View       | Create View
                                                                                             |
---------------------------------------------------------------------------------------------+
| unitedview | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `unitedview` AS select `t
est1`.`company` AS `company` from `test1` union select `test1`.`name` AS `name` from `test1` |
+------------+-----------------------------------------------------------------------------------------------1 row in set (0.01 sec)

mysql> select * from unitedview;
+-----------+
| company   |
+-----------+
| google    |
| yahoo     |
| microsoft |
| gg        |
| yh        |
| msft      |
+-----------+
6 rows in set (0.00 sec)

mysql> select * from unitedview order by company as;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
 for the right syntax to use near 'as' at line 1
mysql> select * from unitedview order by company asc;
+-----------+
| company   |
+-----------+
| gg        |
| google    |
| microsoft |
| msft      |
| yahoo     |
| yh        |
+-----------+
6 rows in set (0.00 sec)

Posted: Tue Jan 24, 2006 4:50 pm
by mikebr
I couldn't find any way of doing this so I thought I would try here just in case there was some smart way of doing it in the same query that I couldn't find. The actual query will need to use 'LIMIT' in it as it will be returning a search results, so in actual fact it will be a bit more complicated than just the simple query I used as an example. The actual query would be along the lines of the following, but then using an array wouldn't give me what I want either in this case.

Code: Select all

$query = @mysql_query("SELECT `cid`, `company`, `name`, `town` FROM `clients` ORDER BY `company`, `name` LIMIT $start_row, $rows_per_page");
Thanks for the replies.

Posted: Tue Jan 24, 2006 5:04 pm
by mikebr
raghavan20, I was kind of thinking something similer, sticking the results in a single column table then querying that table but the fact I would be using 'LIMIT' for performing a search and giving the user a way of browsing through the results say 10 at a time would probably make this unpractical.

Thanks

Posted: Tue Jan 24, 2006 5:10 pm
by raghavan20
I do not really understand what you are saying but I assume that you want to impose limit on the query....

Code: Select all

mysql> select * from unitedview order by company asc;
+-----------+
| company   |
+-----------+
| gg        |
| google    |
| microsoft |
| msft      |
| yahoo     |
| yh        |
+-----------+
6 rows in set (0.00 sec)

mysql> select * from unitedview order by company asc limit 0,2
    -> ;
+---------+
| company |
+---------+
| gg      |
| google  |
+---------+
2 rows in set (0.38 sec)

Posted: Tue Jan 24, 2006 5:21 pm
by mikebr
Yes I would want to impose a limit on the query, but if I did this and someone added an entry to the column after the temp unitedview table was propagated then that new entry wouldn't be available to the user in the current search, it wouldn't be listed in the unitedview table as the table would have had it's values entered before the other user added an extra entry, or is there something I am not seeing here? .

Posted: Tue Jan 24, 2006 5:39 pm
by raghavan20
read about views, views hold only structures, values are got from respective tables dynamically

Posted: Tue Jan 24, 2006 5:47 pm
by mikebr
raghavan20,
OK, I will look into views.

Thanks for your help

Posted: Tue Jan 24, 2006 6:02 pm
by RobertGonzalez
Views are only available in MysQL 5+ I think. What version are you running?

Code: Select all

SELECT VERSION()

Posted: Tue Jan 24, 2006 6:13 pm
by mikebr
mysql (4.0.25-standard)

Guess I'm out of luck.

Posted: Tue Jan 24, 2006 7:47 pm
by raghavan20
But you have to run create temporary table and select from new_table commands every time when you want to sort by columns....

Code: Select all

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.16-nt |
+-----------+
1 row in set (0.11 sec)

mysql>  create table test1(
    ->  company varchar(30),
    ->  name varchar(30));
Query OK, 0 rows affected (0.38 sec)

mysql> insert into test1 values ('google','gg'), ('yahoo','yh'), ('microsoft','m
sft');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create temporary table if not exists new_table
    -> select company from test1
    -> union
    -> select name from test1;
Query OK, 6 rows affected, 1 warning (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0


mysql> select * from new_table order by company asc limit 0, 10;
+-----------+
| company   |
+-----------+
| gg        |
| google    |
| microsoft |
| msft      |
| yahoo     |
| yh        |
+-----------+
6 rows in set (0.00 sec)

Posted: Tue Jan 24, 2006 7:55 pm
by mikebr
But you have to run create temporary table and select from new_table commands every time when you want to sort by columns....
Yes, of course that is a way to do it.

Thanks
Again