CODEWITHSUNDEEP
asp.netsql-server
Matey Johnson
Matey Johnson

Reputation: 227

Get all records arranged by maximum number of columns matched

I'm looking out for a way to get all records from a table arranged or ordered in such a way that maximum number of columns matched are at top and then further

Example: Table Name(Vehicle)

Id     Name    VehicleMileage    VehicleAverage    VehicleCC    Gear
1    Vehicle1     30HP@2000         25000             1000        3
2    Vehicle2     30HP@2000         22000             8000        2
3    Vehicle3     20HP@2000         21000             5000        4
4    Vehicle4     60HP@2000         25000             1000        3
5    Vehicle5     70HP@2000         23000             3000        3

Over here lets assume the searched parameters are as follows:

VehicleMileage: 30HP@2000
VehicleAverage: 25000
VehicleCC: 1000
Gear: 3

The queried result would be in following order:

Vehicle1 (Reason: 4 matched columns)
Vehicle4 (Reason: 3 matched columns)
Vehicle2 (Reason: 1 matched column)
Vehicle5 (Reason: 1 matched column)
Vehicle3 (No matched column)

My database is on SQL server.

Upvotes: 1

Views: 52

Answers (1)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try some thing like this

SELECT Name + ' (Reason: '+CAST(MatchCount AS VARCHAR)+' matched columns)'
FROM (
        SELECT NAME,(CASE WHEN VehicleMileage ='30HP@2000' THEN 1 ELSE 0 END +
                     CASE WHEN VehicleAverage='25000' THEN 1 ELSE 0 END +
                     CASE WHEN VehicleCC =1000 THEN 1 ELSE 0 END +
                     CASE WHEN Gear =3 THEN 1 ELSE 0 END ) AS MatchCount
       FROM TableName
)t
ORDER BY MatchCount

Upvotes: 2

Related Questions