SQL question: How to sort SELECT result

[stndn]

Helo,
I have a question regarding how to sort out SQL search result.
FYI I'm using MySQL 4.1

Let's assume I have the following table 'blah':

id | name
---+------
1 | Don't eat at Mike's
2 | Eat at Joe's
3 | Don't eat at Joe's
4 | Mike eat at Mike's
5 | Joe is not Mike
6 | Joe let go of Mike

Now I want to look for all the record that contains the word Joe.
Should be easy with the following:
SELECT * FROM blah WHERE name LIKE '%JOE%'

With the following result:

2 Eat at Joe's
3 Don't eat at Joe's
5 Joe blow
6 Joe let go


Now, if I want to sort it such that records that begin with 'Joe' proceeds other records, how would I go about it? Basically, I want the output to look like this:

5 Joe blow
6 Joe let go
3 Don't eat at Joe's
2 Eat at Joe's
(The order of 3 and 2 doesn't matter, but it's great if I can start with D before E)


I tried SELECT * FROM blah WHERE name LIKE 'JOE%' OR name LIKE '%JOE%' ORDER BY name but it gave me the records 3, 2, 5, 6.

Well, this could be done with two queries:
SELECT * FROM blah WHERE name LIKE 'JOE%' ORDER BY name
SELECT * FROM blah WHERE name LIKE '_%JOE%' ORDER BY name
But I would like to avoid that if possible (ie: Do the above in one command)

Any hints or ideas?


Thanks.
-stndn.

 

[MrChad]

You could do this:

(SELECT * FROM blah WHERE name LIKE 'JOE%' ORDER BY name)
UNION
(SELECT * FROM blah WHERE name LIKE '_%JOE%' ORDER BY name)

 

[stndn]

Thanks a lot, MrChad. That works! -)

It's weird that the result set is not as expected.. Here's what i got (notice that 'E' comes before 'D'):

5 Joe blow
6 Joe let go
2 Eat at Joe's
3 Don't eat at Joe's

I know I said it's acceptable for the ordering, but still it makes me wonder why.
Upon further reading, I found this is MySQL manual:

ORDER BY for individual SELECT statements within parentheses only has an effect when combined with LIMIT. Otherwise, the ORDER BY is optimized away.

So I modified the query to include an arbitrary limit (there's no LIMIT unlimited, i guess):

(SELECT * FROM blah WHERE name LIKE 'JOE%' ORDER BY name LIMIT 10000000)
UNION
(SELECT * FROM blah WHERE name LIKE '_%JOE%' ORDER BY name LIMIT 10000000)

and that gives better result.

5 Joe blow
6 Joe let go
3 Don't eat at Joe's
2 Eat at Joe's


It also works if I want to return only the second and third result from the query with an additional LIMIT:
(SELECT * FROM blah WHERE name LIKE 'JOE%' ORDER BY name LIMIT 0, 9999999999)
UNION
(SELECT * FROM blah WHERE name LIKE '_%JOE%' ORDER BY name LIMIT 0, 9999999999)
LIMIT 1, 2

Giving:

6 Joe let go
3 Don't eat at Joe's


Anyway, thanks again for the help -)

-stndn.

 

[kpilkington]

Another method:

 

[MrChad]

Originally posted by: kpilkington
Another method:

Wow, nice first post! 🙂

Much cleaner and slicker than my UNION method.

 

[Mark R]

Originally posted by: MrChad
You could do this:

(SELECT * FROM blah WHERE name LIKE 'JOE%' ORDER BY name)
UNION
(SELECT * FROM blah WHERE name LIKE '_%JOE%' ORDER BY name)

Nah.

That won't work (reliably).

A union is allowed to produce its results in any order. If you think about the statment above, you can see that it will optimise to

SELECT * FROM blah WHERE name LIKE 'JOE%' OR name LIKE '_%JOE%'

The ORDER BYs are irrelevant, because the UNION is allowed to change the order. The only time the ORDER BYs may be relevant is if there is a LIMIT.

Anyway, kpilkington has identified the proper way of doing it.

 

[stndn]

Oopss.. just noticed that the data set from my first post is slightly different from what I have on my computer...
(but that's beside the point)

Anyways, I agree that the solution from kpilkington is much cleaner.
It took me a while to figure out what the statement means (I rarely use conditionals in SQL statements), but now I can see how it works. I tried it and it's working well.

Mark R - yah, that's what I figured. In order to make the UNION works with ORDER BY, *each* SELECT has to include LIMIT. It's hard to take care of when we don't know how many records a table can have. Of course, an arbitrary number could be used (like the one they mentioned in the MySQL manual), but I guess it's best if arbitrary LIMITs can be avoided.

Thanks again for the help, everyone!

And kpilkington, welcome to the forum -)
you just had to register to answer my question, huh? ,p ,)


-stndn.