Sorting SQL results so certain results appear at the top

AARRRGH

I hope someone can help me with this.

Let's imagine my table look like this:

ID	Name	Date		Company	
1	Joe	Today		Microsoft
2	Mary	Yesterday	Oracle
3	Tom	Today		Google
4	Pete	Today		Apple
5	Frank	Today		Google

If I do "select * from table order by date DESC" the results will look something like -

1	Joe	Today		Microsoft
3	Tom	Today		Google
4	Pete	Today		Apple
5	Frank	Today		Google
2	Mary	Yesterday	Oracle

Let's say I always want Google's results to appear at the top of the list, but still keep the ordering by date, how would I do this?

3	Tom	Today		Google
5	Frank	Today		Google
1	Joe	Today		Microsoft
4	Pete	Today		Apple
2	Mary	Yesterday	Oracle

Thanks!

Mick L

You can have more than 1 column in your sort list

e.g. "select * from table order by Company ASC, date DESC"

If you want google to be at the top though, you could add another column to your company table which holds the sort order you want

e.g.

Company CSort
Google 1
Microsoft 2
Apple 3

etc
Then use "select * from table order by CSort ASC, date DESC"

seamus

This isn't an ideal thing to be asking MySQL to do, but I can think of two possible hacks:

1. Use a UNION Statement. So..

(SELECT * from table WHERE company = 'Google' order by date DESC)
UNION
(SELECT * from table WHERE company <> 'Google' order by date DESC)

The only issue with this is the second query in the union - it could prove very database heavy.

2. The other option is to create a fulltext index on the company name.

Then the following query should match all "Google"s at the top of the list.

SELECT * FROM table ORDER BY match(company) AGAINST ('Google') DESC, date DESC

showry

You could stick a case statement into the order by:

select id, name, date, company
from table
order by case when company = 'Google' then 1 else 2 end, date desc

AARRRGH

Thanks for the replies everyone. I think the case expression is probably the easiest solution.

Do you know is it possible to do something like -

select id, name, date, company
from table 
order by 
case 
 when company = 'Google' then 1 
 else EVERYTHING ELSE
end 
date desc

As in, sort Google first, and then whatever comes next is unimportant, as long as it is sorted by date?

Thanks!

showry

I think that's what mine does,
everything that's not google gets a value of 2 so they will be sorted by date only.

AARRRGH

Ah yes, I see what you mean, thank you.

Final question - have you ever used case expressions yourself in a database? Did you notice any performance change?

showry

AARRRGH wrote: »

Ah yes, I see what you mean, thank you.

Final question - have you ever used case expressions yourself in a database? Did you notice any performance change?

I use them all the time, can't say I've ever noticed a performance issue.

AARRRGH

Thanks.

I'm after trying this but it's not quite doing what I want it to do.

It is sorting the results by Google and ignoring the date.

For example, I am using this SQL -

select id, name, date, company
from table 
order by 
case when company = 'Google' then 1 else 2 end, date desc

And it is giving me these results -

3	Tom	Today		Google
5	Frank	Today		Google
6	Frank	Yesterday	Google
7	Frank	Last Week	Google
1	Joe	Today		Microsoft
4	Pete	Today		Apple
2	Mary	Yesterday	Oracle

It should give me these results -

3	Tom	Today		Google
5	Frank	Today		Google
1	Joe	Today		Microsoft
4	Pete	Today		Apple
6	Frank	Yesterday	Google
2	Mary	Yesterday	Oracle
7	Frank	Last Week	Google

Do you know what I mean?

Do you have any idea how I can fix this?

Thanks

bonkey

You've told it to sort first by google/not-google, and inside this grouping to sort by date....which is exactly what the output describes.

The desired output appears to be sorting by date, and inside this grouping by google/not-google....so it would look like you've got your order by reversed.

AARRRGH

Would you believe I had thought that was the problem, but then felt that was too easy a solution!

It works a treat.

Thanks.