On 4/8/10 8:08 AM, "ya...@southcot.com" <ya...@southcot.com> wrote:
> I am currently having to work with a system that has to date used a simple > sort. > > e.g. > > Table:Fruit > > Type Name Colour > ------------------- > Hesp Orange Orange > Hesp Lime Green > Hesp Lemon Yellow > Pepo Melon Yellow > Pepo Gourd Multi > True Kiwi Green > True Guava Green > > SELECT * FROM Fruit Order BY Name; > > Giving: > > Pepo Gourd Multi > True Guava Green > True Kiwi Green > Hesp Lemon Yellow > Hesp Lime Green > Pepo Melon Yellow > Hesp Orange Orange > > > I need to set up a select that will return a list where it is ordered by > Colour for the type Hesp and by Name for the rest. Giving: > > > Hesp Lime Green > Hesp Orange Orange > Hesp Lemon Yellow > Pepo Gourd Multi > True Guava Green > True Kiwi Green > Pepo Melon Yellow > > The position of the Hesp items in the result is unimportant. I am unable to > change the PHP code so the change has to be in the select statement. Can > anyone suggest a solution to this? > > Charlie Use a union query...the hesp come first or last Select * From Fruit WHERE type='hesp' order by colour UNION Select * From FRUIT WHERE type <> 'hesp' order by name
