CODEWITHSUNDEEP
mysqlsql
tyteen4a03
tyteen4a03

Reputation: 1932

Return results ordered by total matches on same column

I have the following table:

+---------+------------+
| EntryID | CategoryID |
+---------+------------+
|      11 |         15 |
|      11 |         18 |
|     186 |         15 |
|     186 |         18 |
|     186 |        334 |
|     187 |         15 |
|     187 |         18 |
|     187 |        337 |
|     278 |         15 |
|     278 |         18 |
|     278 |        337 |
|     278 |        457 |
+---------+------------+

I'd like to get results in the order of how relevant the matches are, which is a percentage of CategoryID rows / CategoryIDs specified.

Where I am stuck on is that this calculation needs to work on multiple categoryIDs queries. For example, if I were to look up categoryID 18, entryID 11 should come first because it has a Match Percentage (excuse me for the cliche naming) of 50%, then either 186 or 187 comes second with a Match Percentage of 33% (in my use case, sorting ondoesn't matter), then 278 would come last with a Match Percentage of 25%.

My question is: Is there a way to do this kind of sorting in one/multiple SQL query/queries? Java is available on hand so I can sort with Java instead of MySQL, but I'd like to see if there is a pure-SQL solution. Additionally, is there a better way to calculate the relevance in my case?

(And just for fun, is there a better title to this question?)

Upvotes: 0

Views: 55

Answers (1)

Ike Walker
Ike Walker

Reputation: 65527

It sound likes you want something like this:

select EntryID, count(*)
from your_table
group by EntryID
order by count(*) asc

UPDATE: Based on the updated question, here's a way to accomplish that:

select your_table.EntryID, count(*) as matches, sub_query.total_rows
from your_table
inner join (
  select EntryID, count(*) as total_rows
  from your_table
  group by EntryID
) sub_query on sub_query.EntryID = your_table.EntryID
where your_table.CategoryID = 18
group by your_table.EntryID
order by (count(*) / sub_query.total_rows) desc

Upvotes: 3

Related Questions