Arbitrary sort?

[michaellunsford]

Imagine searching for a product. I want to see all "HP" brand products first, but after all the HP stuff, the other brands shouldn't be put in any particular order.

I'm thinking it would be equivalent to two separate queries: one with `field`='arbitrary_value' and the next `field` != 'arbitrary_value'. Writing two separate queries into the php page would be a pretty dramatic code rewrite, though. Is there a MySQL way to do it?

Thanks!

[effigy]

I'm not sure if this is the best way, but what about a UNION?
[tt]
(select * from products where brand = 'HP' order by name)
union
(select * from products where brand != 'HP' order by rand())
[/tt]

[michaellunsford]

union... That's a new one on me...  8) Works great. That's one I'll have to write down.



hmmm... no "solved" link.

[Barand]

Another way is to create a boolean result to sort on

select * from products order by brand='HP' DESC,  name

[michaellunsford]

now that's cool 8)

[effigy]

Touché Barand.

[fenway]

Yeah... using ORDER BY FIELD() is also a nifty trick...