Ordering select results by column

[mfyahya]

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!

[max]

On 22 Aug 2005 01:42:35 -0700, mf*****@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]

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]

>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