[Resolved] Specific SQL select help..

biz

Say I have a table which looks like this:

mysql> SELECT * FROM t ORDER BY a;
+---+---+
| a | b |
+---+---+
|   | b |
| a |   |
| c |   |
+---+---+
3 rows in set (0.00 sec)

However I wish to order it like this:

mysql> SELECT * FROM t <somthing_here_to_merge_and_sort>;
+---+---+
| a | b |
+---+---+
| a |   |
|   | b |
| c |   |
+---+---+
3 rows in set (0.00 sec)

Is there any way I can somehow 'merge' the two columns of data and sort based on this new column?

Thanks in advance...

stolzyboy

Are you trying ONLY to do it with MySQL, not any PHP?

biz

I am using PHP, however for simplicity I excluded any PHP info, as I am hoping to do the sorting on the MySQL side.

stolzyboy

hmmm... not sure you are gonna get this done with MySQL, even if you use the ORDER BY clause with both columns like:

ORDER BY a, b

that will order by a, then by b, not together

biz

Originally posted by stolzyboy
hmmm... not sure you are gonna get this done with MySQL, even if you use the ORDER BY clause with both columns like:

ORDER BY a, b

that will order by a, then by b, not together

Exactly, which is why I was hoping there was some way to merge the two columns and sort based on that new column of data...

Anyone have an idea? Or is this just not possible with MySQL?

stolzyboy

did a little reading, and as of MySQL 4.0.0 you can do it with a UNION

biz

ah...thats it...thanks 🙂.

biz

Found a link to a site which explains union emulation in MySQL 3.x...thought someone else may find it useful...

http://jinxidoru.com/tutorials/union.html

Lars_Berg

select * from t
order by coalesce(a,b)

stolzyboy

Originally posted by Lars Berg
select * from t
order by coalesce(a,b)
[/B]

apparently, you didn't read the previous posts between us, that won't work, cuz he want to order by 2 columns that are in different columns

he wants all the results from column 1 and column 2 to be ordered together, like his example shows...

and a simple order by a, b just won't cut it

Lars_Berg

Try this

create table t(c1 varchar(10),c2 varchar(10));
insert into t values(null,'b');
insert into t values('a',null);
insert into t values(null,'c');
select*from t order by coalesce(c1,c2);

stolzyboy

if you haven't noticed i pointed him in the right direction (a UNION) and it seems to be working just fine for him

Lars_Berg

What's the problem? I provided an alternative (easier) solution. Do you mean that my solution will not work or what?

stolzyboy

i am not saying it won't work, but it is NOT an easier solution, UNION is quite easy, and requires NO creation of tables to do what he requires, it is a simple function he can use

give the UNION page i linked to a read, it is really quite simple

Lars_Berg

My solution does not require any additional table creation.

I just provided a complete example so you could easily verify that it works.

i am not saying it won't work

You just did say that in a previous post:

apparently, you didn't read the previous posts between us, that won't work, cuz he want to order by 2 columns that are in different columns

I don't see any benefit of continuing this discussion.

stolzyboy

select * from t
order by coalesce(a,b)

-->that won't work in his case

and

create table t(c1 varchar(10),c2 varchar(10));

--> yes you are creating a table

and yes, continuation is futile at this point, i am gonna mark this thread resolved