SQL - select row with most matching columns

Question

I'm new to databases and SQL and trying to solve this problem since 3 days. I have a Java application which queries a SQLite database with JDBC. This works very fine so far. But I cannot figure out the SQL query I need to retrieve the desired rows. The table looks like this:

rowid | application | dstIP          | dstPort | value_to_return
      |             |                |         | 
0     | NULL        | NULL           | NULL    | 26
1     | NULL        | NULL           | 80      | 1
2     | NULL        | 192.168.178.31 | NULL    | 2
3     | NULL        | 192.168.178.31 | 80      | 3
4     | firefox     | NULL           | NULL    | 4
5     | firefox     | NULL           | 80      | 5
6     | firefox     | 192.168.178.31 | NULL    | 6 
7     | firefox     | 192.168.178.31 | 80      | 7

My goal is to get the row where most comlumns match and if no column matches row 0 shall be selected. Here some examples:

input                     -> row

firefox 192.168.178.31 80 -> 7
chrome  192.168.178.31 81 -> 2
chrome  192.168.178.30 82 -> 0
someapp 192.168.178.29 80 -> 1

My best guess so far is this query

SELECT * FROM table WHERE (application IS ? OR application IS NULL)
                      AND (dstIP IS  ? OR dstIP IS NULL)
                      AND (dstPort IS ? OR dstPort IS NULL)
                      ORDER BY application;

The ?s are replaced with the corresponding input values. This query returns row 0 in case of no match. But in case of several matches it returns several rows of course.
I could select the row I need in the Java application, but I want the database to this work for me.
I could change the database if a stored procedure would be the better choice for this problem, because SQLite does not support that.

I hope I described the problem precise enough. Any help will be appreciated.

LS_ᴅᴇᴠ · Accepted Answer · 2013-10-24 10:36:36Z

8

This should do the trick:

SELECT * FROM (
    SELECT *, CASE application WHEN ? THEN 1 WHEN NULL THEN 0 ELSE NULL END
            + CASE dstIP WHEN ? THEN 1 WHEN NULL THEN 0 ELSE NULL END
            + CASE dstPort WHEN ? THEN 1 WHEN NULL THEN 0 ELSE NULL END AS Matches
    FROM table WHERE Matches IS NOT NULL
) GROUP BY application, dstIP, dstPort ORDER BY Matches DESC;

Matches column will count all column match or be NULL when mismatch.

GROUP BY without aggregate functions will catch first row (I hope!), which is max match because inner query is sorted descending.

EDIT: New version:

SELECT *, CASE WHEN application IS ? THEN 1 WHEN application IS NULL THEN 0 ELSE NULL END
        + CASE WHEN dstIP IS ? THEN 1 WHEN dstIP IS NULL THEN 0 ELSE NULL END
        + CASE WHEN dstPort IS ? THEN 1 WHEN dstPort IS NULL THEN 0 ELSE NULL END AS Matches
FROM t
WHERE Matches IS NOT NULL
ORDER BY Matches DESC
LIMIT 1;

Advantages: You can compare NULL also. Disvantages: only 1 match is showed when equally ranked matches are found.

edited Oct 24, 2013 at 10:36

answered Oct 24, 2013 at 9:49

LS_ᴅᴇᴠ

11.2k1 gold badge27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BusDriverJoe Over a year ago

It looks good but I cannot test it. It says syntax error near WHERE. I'm just too unexperienced with SQL :(

BusDriverJoe Over a year ago

I'm testing the inner SELECT at the moment. It seems when there is no match at one column this doesn't work because the ELSE NULL branch. Then the Matches column should be 2 but it is NULL. But I got the basic idea how this works. Give me a while =)

BusDriverJoe Over a year ago

When matches are ranked equally I will select the maximum value to return, so this should be no problem. Give me a while to test it =)

BusDriverJoe Over a year ago

Nice, I ended up with almost the same, I edited it in your answer. Thx a lot for your help! It confuses me that you can add "virtual rows" in SQL for operations.Learned something new =)

Collectives™ on Stack Overflow

SQL - select row with most matching columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related