|
Home > Archive > MySQL Server Forum > August 2005 > Ordering select results by column
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Ordering select results by column
|
|
| mfyahya@gmail.com 2005-08-22, 7:24 am |
| I have a table with three text columns and one time column (say text1,
text2, text3, and time). I wrote a query to select the rows where any
of
the text columns contains a certain keyword:
select * from table1 where
text1 like 'keyword' OR
text2 like 'keyword' OR
text3 like 'keyword';
Now I need to order the
results so that rows associated with text1 are listed first, then
text2,
then text3; and also have the results from each column sorted by
time. Is there a way to do this in mysql? Thanks!
| |
|
| On 22 Aug 2005 01:42:35 -0700, mfyahya@gmail.com wrote:
>I have a table with three text columns and one time column (say text1,
>text2, text3, and time). I wrote a query to select the rows where any
>of
>the text columns contains a certain keyword:
>select * from table1 where
> text1 like 'keyword' OR
> text2 like 'keyword' OR
> text3 like 'keyword';
>
>
> Now I need to order the
>results so that rows associated with text1 are listed first, then
>text2,
>then text3; and also have the results from each column sorted by
>time. Is there a way to do this in mysql? Thanks!
Would it fit your scheme if you went with separate SELECTs -
select * from table1 where text1 like 'keyword' order by time
select * from table1 where text2 like 'keyword' order by time
select * from table1 where text3 like 'keyword' order by time
I suppose you could either print them out as the query runs, or stick them
into arrays???
| |
| Bill Karwin 2005-08-22, 8:23 pm |
| max wrote:
> select * from table1 where text1 like 'keyword' order by time
union all
> select * from table1 where text2 like 'keyword' order by time
union all
> select * from table1 where text3 like 'keyword' order by time
Regards,
Bill K.
| |
| Gordon Burditt 2005-08-22, 8:23 pm |
| >I have a table with three text columns and one time column (say text1,
>text2, text3, and time). I wrote a query to select the rows where any
>of
>the text columns contains a certain keyword:
>select * from table1 where
> text1 like 'keyword' OR
> text2 like 'keyword' OR
> text3 like 'keyword';
>
>
> Now I need to order the
>results so that rows associated with text1 are listed first, then
>text2,
>then text3; and also have the results from each column sorted by
>time. Is there a way to do this in mysql? Thanks!
You can order by an expression. Now construct an expression that
gives you the order you want.
order by
if(text1 like 'keyword', 1, if (text2 like 'keyword', 2,
if (text3 like 'keyword', 3, 4))),
time
This presumes that there aren't any rows where more than one column
matches, or if there are, it is ordered like it would be if only the
first matching column counted.
You may want to do the query as:
text1 like '%keyword%'
or text1 like '% keyword %'
depending on how you lay out your columns.
text like 'key' matches only 'key'.
text like '%key%' matches 'key' and 'keyword' and 'monkey'.
text like '% key %' matches ' key ' and ' gerbil key hamster ' but
not ' monkey '.
but if you want the first or last keywords to match, you need leading
and trailing spaces in the column.
Gordon L. Burditt
|
|
|
|
|