How do you order by the most popular matches?

[Guest_imported]

Hi,

I'm using an Access 97 database (and IIS4) and would like to know whether the following query is possible.

I've written the query in broken English in order to make my intention transparent.

SELECT * FROM Fruits WHERE Fruit="Apple" OR Color="Green" OR Shape="Round" OR Texture="Smooth" ORDER BY the rows that have the most of these attributes first.

Thanks

Marcus

P.S. Responses were given to this question in the ANSI SQL which I think was the wrong forum for my question. Although those responses were very much appreciated I could get neither of them to work.

 

[tlbroadbent]

Sorry that I didn't read your post in ANSI SQL more carefully. Here is an Access version of my solution.

SELECT *,
IIF([Fruit] = "Apple", 1,0) +
IIF([Color] = "Green", 1, 0) +
IIF([Shape] = "Round", 1, 0) +
IIF([Texture] = "Smooth", 1, 0) As AttrCnt

FROM Fruits
WHERE Fruit="Apple"
OR Color="Green"
OR Shape="Round"
OR Texture="Smooth"

ORDER BY
IIF([Fruit] = "Apple", 1,0) +
IIF([Color] = "Green", 1, 0) +
IIF([Shape] = "Round", 1, 0) +
IIF([Texture] = "Smooth", 1, 0) Desc Terry

http://members.home.net/tlbroadbent/index.htm

 

[Cagliostro]

SELECT t.*, Fruits.* FROM
(
select count(*) as x, YourChoise from Fruits
WHERE Fruit="Apple" OR Color="Green" OR Shape="Round"
OR Texture="Smooth"
group by YourChoise
) as t inner join Fruits on
t.YourChoise = Fruits.YourChoise
order by t.x desc John Fill

ivfmd@mail.md

 

[Guest_imported]

Thank you both very much for your help. I got it working with the first suggestion. The second suggestion may have worked, but unfortunately the "YourChoice" assumed too much of me (I'm a novice in SQL), and so I couldn't be sure if I was getting what I should have given my choice.

Thanks again.

Marcus