> > (Select * From Fruit WHERE type='hesp' order by colour) > > UNION > > (Select * From FRUIT WHERE type <> 'hesp' order by name): > > > > The MySQL is still version 4.1.22 would this be the problem? Whatever, I > > have > > a solution now, many many thanks. > > > > Charlie > I think it's a problem with how I used the order by - it's still early here > so I forgot how uniins work - the parnthesis are the proper way to do it. > > What I usually do in table creation if the table is going to require certain > portions to be sorted differently when found together is create a column > that is keyed for this purpose specifically - that way you can use that > 'sorting column' (or columns) as a solution to this so that when your table > grows the problem doesn't need to be solved each time (though this unioin > will solve it if hesp is the only one you need to sort by colour - ever) >
When I examined the result more closely I realised that this was still not working. In fact the ORDER BY statements made no difference to the outcome at all. Playing with it I finally found the syntax that works:- (SELECT * FROM Fruit WHERE Type='hesp' GROUP BY Colour) UNION (SELECT * FROM FRUIT WHERE Type <> 'hesp' GROUP BY Name);
