Imagine a "house" table in a database has 7 foreign keys:

• Location
• Price
• Population
• Size
• Schools
• Shops
• Transport

(Some of the relationships are in fact many to many relationships).

I want to show a list of houses, sorted in descending order by the number of matching foreign keys.

The best result would be where all 7 foreign keys have values, including a large number of values in the many to many relationships.

The next best result would be where all 7 foreign keys have values, but have fewer values in the many to many relationships.

The next best result would be where 6 of the foreign keys have a value.

The next best result would be where 5 of the foreign keys have a value.

And so on until only 1 of the foreign keys has a value.

Is there a way to do this type of sorting in SQL?

Thank you.

It will be not very simple because you must check all the foreign keys, so you must tell all this 7 conditions. You can put in order by eg.:

select
    ...
from
    thistable
order by
    case when exists (select 1 from table1 where id=thistable.fk1) then 1 else 0 end +
    case when exists (select 1 from table2 where id=thistable.fk2) then 1 else 0 end +
    ...
    desc

So the more foreign key have connected record(s) the higher the value will be.

more