CODEWITHSUNDEEP
mysqlsql
Manoj Kumar
Manoj Kumar

Reputation: 322

select data from db based on priority of matched columns in where clause

Hi I am facing one problem, I have a situation that I need to select data from db based on priority of matched columns in where clause

ex. I have a table 

Employee: FirstName, LastName, City, State.

I want to search employee's records based on criteria. but the situation is that first of all I need to get those records where all the four criteria (columns) matched, then all those records where three of four criteria(Columns) matched, then all those records where two of four criteria (Column) matched so on..

For that I am creating dynamic query based on situations, but if search criteria will extend more, than I need to put many conditions in query it will difficult to manage the dynamic query. So is there any other way to handle this problem?

Many Thanks in advance.

Upvotes: 3

Views: 552

Answers (2)

Alex
Alex

Reputation: 17289

You can use your conditions as +1 to rank your record.

http://sqlfiddle.com/#!9/f65790/3

SELECT FirstName, LastName, City, State
FROM Employee
ORDER BY (FirstName = 'John') 
         + (LastName='Snow') 
         + (City='Winterfell') 
         + (State='Nord') DESC

Or if you need to check that rank somewhere downthere:

SELECT FirstName, LastName, City, State,
        (FirstName = 'John') 
         + (LastName='Snow') 
         + (City='Winterfell') 
         + (State='Nord') Rank
FROM employee
ORDER BY Rank  DESC;

Upvotes: 0

Rich Benner
Rich Benner

Reputation: 8113

This has been tested on SQL Server but should give you an idea on MySQL if it doesn't work right away;

Test Data

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData
GO
CREATE TABLE #TestData (FirstName varchar(20), LastName varchar(20), City varchar(20), State varchar(20))
INSERT INTO #TestData (FirstName, LastName, City, State)
VALUES
('Jon','Snow','Winterfell','Westeros')
,('Jaime','Lannister','Riverrun','Westeros')
,('Margaery','Tyrell','Kings Landing','Westeros')
,('Cersei','Lannister','Kings Landing','Westeros')
,('Daenerys','Targaryen','Mereen','Essos')
,('Davos','Seaworth','Winterfell','Westeros')
,('Theon','Greyjoy','Mereen','Essos')
,('Tyrion','Lannister','Mereen','Essos')
,('Petyr','Baelish','Winterfell','Westeros')

Query with the search criteria set as variables. The case will check how many matches we have and order by that field.

DECLARE @Variable1 varchar(20); SET @Variable1 = 'Jon'
DECLARE @Variable2 varchar(20); SET @Variable2 = 'Snow'
DECLARE @Variable3 varchar(20); SET @Variable3 = 'Winterfell'
DECLARE @Variable4 varchar(20); SET @Variable4 = 'Westeros'


SELECT 
FirstName
,LastName
,City
,State
,CASE WHEN FirstName = @Variable1 THEN 1 ELSE 0 END +
 CASE WHEN LastName = @Variable2 THEN 1 ELSE 0 END +
 CASE WHEN City = @Variable3 THEN 1 ELSE 0 END +
 CASE WHEN State = @Variable4 THEN 1 ELSE 0 END Matches

FROM #TestData
ORDER BY (
            CASE WHEN FirstName = @Variable1 THEN 1 ELSE 0 END +
            CASE WHEN LastName = @Variable2 THEN 1 ELSE 0 END +
            CASE WHEN City = @Variable3 THEN 1 ELSE 0 END +
            CASE WHEN State = @Variable4 THEN 1 ELSE 0 END
        ) DESC

Which gives the result as;

FirstName   LastName    City            State       Matches
Jon         Snow        Winterfell      Westeros    4
Davos       Seaworth    Winterfell      Westeros    2
Petyr       Baelish     Winterfell      Westeros    2
Jaime       Lannister   Riverrun        Westeros    1
Margaery    Tyrell      Kings Landing   Westeros    1
Cersei      Lannister   Kings Landing   Westeros    1
Daenerys    Targaryen   Mereen          Essos       0
Theon       Greyjoy     Mereen          Essos       0
Tyrion      Lannister   Mereen          Essos       0

Upvotes: 3

Related Questions