# Selecting the correct row given multiple input value combinations

I have a table that stores part numbers for various items like so:

``````+------+-------------+-------------+-------------+
| Item |    Color    |    Type     | Part Number |
+------+-------------+-------------+-------------+
| Pen  |             |             |         001 |
| Pen  |       red   |    felt     |         002 |
| Pen  |      blue   |    felt     |         003 |
| Pen  |             |    felt     |         004 |
| Pen  |     green   |    felt     |         005 |
| Pen  |             |     gel     |         006 |
| Pen  |     green   |     gel     |         007 |
+------+-------------+-------------+-------------+
``````

I need to get the part numbers for items that were ordered in the past. Is there a way to match the "correct" row with a single query knowing the Item, Color and Type?

Given {Item = Pen, Color = orange, Type = felt} the result should be 004

Given {Item = Pen, Color = blue, Type = felt} the result should be 003

Given {Item = Pen, Color = blue, Type = gel} the result should be 006

I'd like to avoid populating every possible combination into the table if possible.

• I don't see how `item = pen`, `color = blue` and `type = gel` gets you `006`, is there a typo or what is the logic that you are using? Also where is the `color = orange` to get a result of `004`? Do you want the row that returns the most items matched? May 13, 2015 at 19:03
• Yes, the maximum matches. The idea is that some items are grouped: all 'gel pens' that aren't green are 006, which includes 'orange'. May 13, 2015 at 19:12
• What if there is an extra row with `item = pen, color = blue, type = NULL, part_number=008`. What should the query `item = pen, color = blue and type = gel` return then? `006` or `008`? Both would have two matches. May 13, 2015 at 19:12
• @ypercube That would be fine (as it would be an input error) EDIT: I mean that it is impossible since that cannot happen without the table being wrong. May 13, 2015 at 19:14
• `ORDER BY (pen='blue')+(type='gel')+(color='blue') DESC, (pen='blue')+(type='gel') DESC, (pen='blue') DESC LIMIT 1;` May 13, 2015 at 19:21

Short and dirty answer. Not efficient, it will have to scan the whole table. Will give only 1 row as result, if there are more than one rows with same (maximal) number of matches ("wrong" data in your terminology), the choice is arbitrary:

``````SELECT *
FROM tableX
ORDER BY (item='pen')+(type='gel')+(color='blue') DESC
LIMIT 1 ;
``````

Slightly better than the above but not more efficient. If there are more than one rows with same (maximal) number of matches, the choice is not arbitrary but based on the assumption that a row with "item and type" is preferred over a row with "item and colour" which is preferred over a row with "type and colour":

``````SELECT *
FROM tableX
ORDER BY (item='pen')+(type='gel')+(color='blue') DESC,
(item='pen')+(type='gel') DESC,
(item='pen') DESC
LIMIT 1 ;
``````
• Maybe better (shorter)?: `ORDER BY 4*(item='pen')+2*(type='gel')+(color='blue') DESC` May 13, 2015 at 19:52
• @RickJames for the 2nd query? Yes, nice! May 13, 2015 at 19:53