Can order by the most attributes matched be done in a SQL statement?

[Guest_imported]

Hi,

I'm using an Access database and would like to know whether the follow 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

 

[Cagliostro]

is it good?
select t.x as x, y.*
(select a, count(a) as x from y group by a) as t inner join y on t.a = y.a
order by x John Fill

ivfmd@mail.md

 

[tlbroadbent]

How about this...

SELECT
Cnt=CASE Fruit WHEN 'Apple' Then 1 Else 0 End +
CASE Color WHEN 'Green' Then 1 Else 0 End +
CASE Shape WHEN 'Round' Then 1 Else 0 End +
CASE Texture WHEN 'Smooth' Then 1 Else 0 End,
*

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

ORDER BY 1 Desc Terry

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