CODEWITHSUNDEEP
mysqlsql
Zeeshan Faiz
Zeeshan Faiz

Reputation: 11

Any way to sort the Rows Fetch in SQL Using matching Criteria

Im a bit new in SQL. What im trying to do is to sort the row in descending order of matching criteria. When I say matching criteria i mean the those rows that have more matching columns with my SQL Query should come first. Like

SELECT a.*, 
       f.* 
FROM   a  
       INNER JOIN f as f
               ON f.fid = a.aid 
WHERE  ( a.icid = 25290 
         AND a.pt = 2 ) 
       AND ( f.st LIKE '%Outpatient%' 
              OR f.st LIKE '%Service201%' )

Now the order of my rows should be that if it matches all it should come on the top, then on second should be the one that have matches all the columns but F.ST , then so on so forth.

example

i have 5 columns in a table name Test the name of columns are

id, name, fname, city, country

My Query 

SELECT * 
FROM TEST 
WHERE name LIKE "%John%" 
   OR fname LIKE "%Mike%" 
   OR city IN ("Abu Dhabi, New York, Manchester")
   OR country IN ("USA, UAE, UK")

Say that i have 10 rows against this query. What i want is that the row that matches all should come in top of result set like following

name, fname, city, counrty

John, Mike, New York, USA -> matches all four fields

John, Mike, Delhi, UK -> matches three fields

John, Khan, Lahore, USA -> matches two fields

John, Khan, Lahore, PK -> matches one field

John Mike, Khan, Lahore, PK -> matches one field

Upvotes: 1

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

MySQL will treat boolean expressions as integers, in a numeric context. This makes your question easy to answer:

SELECT a.*, f.* 
FROM a INNER JOIN
     f f
     ON f.fid = a.aid 
WHERE a.icid = 25290 AND
      a.pt = 2 AND
      ( f.st LIKE '%Outpatient%' OR f.st LIKE '%Service201%' ) 
ORDER BY ( (f.st LIKE '%Outpatient%') + (f.st LIKE '%Service201%') ) DESC;

Note: You can put the same expression in the SELECT, if you like:

SELECT . . .,
       ( (f.st LIKE '%Outpatient%') + (f.st LIKE '%Service201%')
       ) as numMatches
. . .

Upvotes: 2

Stephan Lechner
Stephan Lechner

Reputation: 35154

Let me answer your question based on your second statement; you can then apply this strategy to other cases. Assume that you want to weight several criteria; Then you could - for each criteria - use an if-statement that returns 0 if it is not fulfilled and "some weight" if it is fulfilled. Add these weights per criteria in a subquery and "order by" this weight in the outer query:

Select name, fname, city, country
from 
  (SELECT *, (if(name LIKE "%John%"),8,0)
           + if(fname LIKE "%Mike%", 4,0)  
           + if(city IN ("Abu Dhabi, New York, Manchester"), 2,0)  
           + if(country IN ("USA, UAE, UK"), 1,0) ) as weight
   FROM TEST)  weightedTest
where weight > 0
order by weight desc

Of course you can adapt the weights according to your demand; If you simply want to count how many criteria are fulfilled, you can assign a weight of 1 to each of the criteria.

Upvotes: 0

Related Questions