How to Find the Most Specific Matching Row in Oracle SQL

Question

I need to be able to run a query that allows me to find the most specific match only. What I mean is that I have a table with a subject, category and type column and I want to write one query that will select only the row that matches all 3 of these columns. If there is no row matching all 3 columns it will select the row that matches subject and category. If there is no category it will select rows that match subject.

I think that I have seen something using the function LEAST that looked similar to this:

SELECT LEAST(
    (SELECT * FROM TABLE_1 WHERE AMT < 10),
    (SELECT * FROM TABLE_1 WHERE AMT > 10)
)

So I was just wondering if maybe something similar could be done for finding the most specific match or if some other approach would be needed.

Thanks in advance for any help.

Answer 1

If you want just one row, you can filter the table on the less restrictive condition, then order the result with a case expression, and finally keep the top row only.

select t.*
from mytable t
where subject = :subject
order by case
    when category = :category and type = :type then 0
    when category = :category then 1
    else 2
end fetch first 1 row only

If there may be more than one row that satisfies one of the condition, you can use fetch first 1 row with ties instead.