Order results by column dynamically depending on where a match was found

Question

Hi,

I'm selecting data from a table where one of 3 columns matches a given search string:

SELECT * 
FROM CARS
WHERE MODEL LIKE searchString
OR MANUFACTURER LIKE searchString
OR DESCRIPTION LIKE searchString

But I need to order the results depending on where the match was found, based on a column weighting. I.e. if the match was found in the MODEL column then the row should appear higher in the results than if the match was found in the MANUFACTURER column. With the weighting being 1-MODEL 2-MANUFACTURER 3-DESCRIPTION.

All help hugely appreciated, thanks!

Answer 1

Oracle's Full Text Search (FTS) functionaltiy--Oracle Text-- would be both faster and provide a rank score based on it's algorithm...

SELECT c.*,
            CASE 
                WHEN c.model LIKE searchstring THEN 1
                WHEN c.manufacturer LIKE searchstring THEN 2
                WHEN c.description LIKE searchstring THEN 3
            END AS match_rank
    FROM CARS c
  WHERE c.model LIKE searchstring
        OR c.manufacturer LIKE searchstring
        OR c.description LIKE searchstring
ORDER BY match_rank 

But you don't mention how to handle the ranking if two more more columns match....