MySQL query to choose top camera choices based on multiple column values

Question

Okay so I am trying to get some ideas on how to go about determining the top choices of camera I have from the table setup below. This is not a real scenario I am just trying to figure out the best way or some way to go about doing this.

So the camera table has entries id(INT) which is unique, name(VARCHAR), reviewRank(INT), price(INT), and failRate(INT). Here is an example of the TABLE setup and inserts code:

CREATE TABLE CAMERA(
id INTEGER,
name VARCHAR(30),
reviewRank INT,
price INT,
failRate INT,
PRIMARY KEY(id))ENGINE=INNODB;

INSERT INTO CAMERA VALUES(1,'Camera 1',5,100,1);
INSERT INTO CAMERA VALUES(2,'Camera 2',4,300,7);
INSERT INTO CAMERA VALUES(3,'Camera 3',1,10,99);
INSERT INTO CAMERA VALUES(4,'Camera 4',1,10,6);

Criteria is that a higher reviewRank is better, a lower price is better, and a lower failRate is better. And if multiple cameras share the best value for one of the columns then we choose the one with the best values in the other two columns or if multiple cameras have the same values in multiple columns such as CAMERA(3,'Camera 3',1,10,99) and CAMERA(4,'Camera 4',1,10,6) do then the last column determines the outcome.

Basically any camera with the best value in any of the three columns automatically becomes a candidate for the list of top cameras to choose from. But if multiple cameras have the best value for the same column then we need to determine which is the best of these by looking at the values of the other columns.

So using the database state above the query would return cameras (1,'Camera 1',5,100,1) and (4,'Camera 4',1,10,6) since camera id 1 has the highest reviewRank and lowest failRate and camera id 4 and camera id 3 tie for having the lowest price but camera id 4 has a lower failRate than camera id 3.

So basically I am trying to figure a way to go about finding the top choices where there are multiple column comparisons using MySQL preferably using a single query if possible.

My initial thoughts were something like this

SELECT DISTINCT C.* 
FROM CAMERA C
WHERE C.reviewRank>=ALL(SELECT reviewRank FROM CAMERA)
OR    C.price<=ALL(SELECT price FROM CAMERA)
OR    C.failRate<=ALL(SELECT failRate FROM CAMERA);

However this is obviously wrong since it does not cover all of the possible permutations. And after doing quite a bit of searching for a way to go about this I still have no idea how to go about this.

Michael Fredrickson · Accepted Answer · 2012-03-20T02:26:32.500

0

I believe that the ORDER BY clause accomplishes what you're looking for:

SELECT topC.Id, topC.Name, topC.ReviewRank, topC.Price, topC.FailRate
FROM Camera topC JOIN (
    (SELECT c.ReviewRank, c.Price, c.FailRate
    FROM Camera c
    ORDER BY 
        c.ReviewRank DESC,
        c.Price,
        c.FailRate
    LIMIT 1)
    UNION 
    (SELECT c.ReviewRank, c.Price, c.FailRate
    FROM Camera c
    ORDER BY 
        c.Price,
        c.FailRate,
        c.ReviewRank DESC
    LIMIT 1)
    UNION 
    (SELECT c.ReviewRank, c.Price, c.FailRate
    FROM Camera c
    ORDER BY 
        c.FailRate,
        c.Price,
        c.ReviewRank DESC
    LIMIT 1)
) values 
    ON values.ReviewRank = topC.ReviewRank
    AND values.Price = topC.price
    AND values.FailRate = topC.FailRate

edited Mar 20 '12 at 02:26

answered Mar 19 '12 at 21:41

Michael Fredrickson

36,839
5
92
109

Both Camera (1,'Camera 1',5,100,1) and (4,'Camera 4',1,10,6) need to be returned since (1,'Camera 1',5,100,1) has the best overall reviewRank and also has the best failRate so it should be considered as a top choice. Camera (4,'Camera 4',1,10,6) shares the best price with camera (3,'Camera 3',1,10,99) but has a better failRate so Camera (4,'Camera 4',1,10,6) should also be returned – BobPotter Mar 19 '12 at 22:41
@BobPotter Ah, I understand better now... so you need a single item from each of your three categories, but if the same item is the best for multiple categories, then it should only be returned once. Please see my updated query. – Michael Fredrickson Mar 19 '12 at 23:04
The code snippet you posted does appear to work correctly with the given state. I think you understand the problem correctly. To clarify, the result does not necessarily have to be a single item from each of the three categories. The query could return only one tuple in the case where that tuple had the best rank, best price, and best failRate. So yeah, basically what you stated I think. – BobPotter Mar 19 '12 at 23:25
Okay I am trying to understand how this works. My understanding is we are selecting one from the highest reviewRank, the the lowest price, and the lowest failRate using the code in each set of parentheses. Then using the UNION rids the duplicates. The problem with this is it could be possible to have more than one unique camera with the same specs and the code above would only choose one, say for example we have (1,'Camera 1',1,10,6) (2,'Camera 2',1,10,6) the result would only be one of these. – BobPotter Mar 20 '12 at 00:04
@BobPotter Ah.. so if there's two cameras with the same values, and both should be returned, we can change the union statement to only return the order criteria values... and then select all items with values that match those criteria. Please see my additional edit. – Michael Fredrickson Mar 20 '12 at 02:27
Kinda convoluted, but I guess that is expected with more complicated queries. The edited code appears to meet the criteria for the query. Thanks for the help. – BobPotter Mar 21 '12 at 00:38
@BobPotter Glad it helped... if this post answered your question, [please mark it as the accepted answer](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). – Michael Fredrickson Mar 21 '12 at 03:06
I was hoping to get more input from other users, but I guess there will be none. Was hoping to see more alternatives. Your approach did work though. Thanks again. – BobPotter Apr 07 '12 at 00:57

MySQL query to choose top camera choices based on multiple column values

1 Answers1