ORDER BY IN SQL ? 1

[therealeasterbunny]

Hi,

I'm a newbie when it comes to SQL but have managed so far to query an MS Access DB for various stuff for my Intranet reporting purposes. However I have come across a problem.

I would like to create a report that queries an Access database to sort by most selected fields. I will try and explain further...

Lets say we have a table like this

my_name my_fruit
------------------
STEVE APPLE
TONY ORANGE
PAUL APPLE
JOHN ORANGE
FRED CHERRY
ANDREW ORANGE
JACOB ORANGE

I would an SQL statement to give me the my_fruit column sorted by MOST first (i.e. 4x oranges, then 2 apples, then 1 cherry), so it would return a recordset like ...

my_name my_fruit
------------------
ANDREW ORANGE
JACOB ORANGE
JOHN ORANGE
TONY ORANGE
PAUL APPLE
STEVE APPLE
FRED CHERRY

The best query I can get is "SELECT * FROM MY_TABLE ORDER BY my_fruit, my_name"

but this would sort it by Apple first, then cherry, then orange. Is there a way to say instead of ORDER BY to give me the above table returned (i.e. ORANGES, APPLES, then CHERRY).

Many thanks in advance.

 

[BNPMike]

1 Do a count by fruit. This gives you:

Orange 4
Apple 2
etc

2 Join this back to your table on Fruit and create a query(recordset) that adds the count. You now have

STEVE APPLE 2
TONY ORANGE 4
PAUL APPLE 2
JOHN ORANGE 4
FRED CHERRY 1
ANDREW ORANGE 4
JACOB ORANGE 4

3 Now order by count, fruit and person. mike.stephens@bnpparibas.com

 

[therealeasterbunny]

Thanks very much BNPMike, I'll have a go at trying to create the statement ...