Is there a SQL technique for ordering by matching multiple criteria?

Question

I have several tables that get JOINed together to form a table with columns

designID
garmentID
colorID
sizeID
imageID

I have a function that looks like this [variables in square brackets are optional]:

getProductImages($designID, [$garmentID], [$colorID], [$sizeID]);

I want it to return all imageIDs that match $designID in the following order:

• Rows that match $garmentID, $colorID, and $sizeID first
• Rows that match $garmentID and $colorID next
• Rows that match just $garmentID next
• Rows that match none (just $designID) last

I could do this pretty easily by just loading all the rows that match $designID and then sorting them in PHP, but my understanding is that it's generally faster to do sorting in MySQL when possible. There will be about 20 rows matching a given $designID.

So my question is twofold: Is it worth doing the sorting in a SQL statement? If I do, what is the best approach to take?

I would also be very interested to know if there is a name for this kind of sorting.

Answer 1

If I understood correctly, it looks like you can use expressions in your ORDER BY, in a way similar to the accepted answer given to the following Stack Overflow post:

• Using MySql, can I sort a column but have 0 come last?

Therefore, your query might look like this:

SELECT    imageID
FROM      ...
JOIN      ...
WHERE     designID = 100          
ORDER BY  garmentID = 1 DESC,
          colorID = 5 DESC,
          sizeID = 10 DESC;

Note that garmentID, colorID, and sizeID are not used as filters in the WHERE clause. The values are only used in the ORDER BY expressions.

Test case:

CREATE TABLE designs (designID int, garmentID int, colorID int, sizeID int);

INSERT INTO designs VALUES (100, 1, 1, 1);
INSERT INTO designs VALUES (100, 1, 2, 2);
INSERT INTO designs VALUES (100, 1, 5, 3);
INSERT INTO designs VALUES (100, 1, 5, 10);
INSERT INTO designs VALUES (100, 1, 5, 15);
INSERT INTO designs VALUES (100, 1, 8, 20);
INSERT INTO designs VALUES (100, 2, 5, 10);
INSERT INTO designs VALUES (100, 2, 6, 15);
INSERT INTO designs VALUES (101, 1, 1, 1);
INSERT INTO designs VALUES (101, 2, 1, 1);

Result:

SELECT    * 
FROM      designs 
WHERE     designID = 100 
ORDER BY  garmentID = 1 DESC, 
          colorID = 5 DESC, 
          sizeID = 10 DESC;

+----------+-----------+---------+--------+
| designID | garmentID | colorID | sizeID |
+----------+-----------+---------+--------+
|      100 |         1 |       5 |     10 |
|      100 |         1 |       5 |      3 |
|      100 |         1 |       5 |     15 |
|      100 |         1 |       1 |      1 |
|      100 |         1 |       2 |      2 |
|      100 |         1 |       8 |     20 |
|      100 |         2 |       5 |     10 |
|      100 |         2 |       6 |     15 |
+----------+-----------+---------+--------+
8 rows in set (0.02 sec)

Note how the row that matches the specified garmentID, colorID and sizeID is first. Failing that, the rows that match garmentID and colorID are next. Then the rows that only match garmentID follow. Then the rest, which only match the designID filter of the WHERE clause.

I believe it is worth doing this in SQL. As @Toby noted in the other answer, in general you don't need to worry about performance when sorting such a small number of rows, assuming you will always be filtering by designID... As for your other question, I don't know if there is a name for such a query - I tend to call it "ordering by an expression".

Answer 2

This is an interesting problem, and reading Daniel's answer has taught me something (complicated) I didn't know about SQL.

However,

If you are only ever likely to have 20 such designs, the reality is that the sorting is going to be just as fast in php or MySQL. Often, unless you're dealing with 1000 or millions of rows, speed isn't an issue.