Selecting the correct row given multiple input value combinations

Question

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? — Taryn
– Taryn, Commented 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'. — Maximillian
– Maximillian, Commented 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. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented 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. — Maximillian
– Maximillian, Commented 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; — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented May 13, 2015 at 19:21

Enthusiast · Accepted Answer · 2015-05-13 19:51:14Z

3

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 ;

edited May 13, 2015 at 19:51

Enthusiast

2152 silver badges12 bronze badges

answered May 13, 2015 at 19:44

ypercubeᵀᴹ

100k13 gold badges217 silver badges306 bronze badges

Maybe better (shorter)?: ORDER BY 4*(item='pen')+2*(type='gel')+(color='blue') DESC

Rick James
– Rick James

2015-05-13 19:52:40 +00:00
Commented May 13, 2015 at 19:52
@RickJames for the 2nd query? Yes, nice!

ypercubeᵀᴹ
– ypercubeᵀᴹ

2015-05-13 19:53:16 +00:00
Commented May 13, 2015 at 19:53

Add a comment |

Stack Exchange Network

Selecting the correct row given multiple input value combinations

1 Answer 1

Your Answer

Hot Network Questions

Selecting the correct row given multiple input value combinations

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions