ORDER BY field from a comma separated subquery

Question

I'm trying to order a query based on the comma separated result of another query

SELECT t.field1, t.field2 /*, ...*/
FROM table t
/* ... */
ORDER BY 
    CASE WHEN NOT EXISTS (SELECT 1 FROM table1 t1 WHERE t1.field1sub = t.field1) 
        THEN FIELD(t.field2, (SELECT field_order FROM table1 t1 WHERE t1.field1sub = t.field1))
        ELSE FIELD(t.field2, (SELECT field_order FROM table1 t1 WHERE t1.field1sub = 0))
    END

The important part of the query is this

FIELD(t.field2, (SELECT field_order FROM table1 t1 WHERE t1.field1sub = t.field1))

So basically, the subquery returns a comma separated string 1,2,3,7,9,4,10, and I want to order by those fields first.

I know we can do for example ORDER BY FIELD(t.field,2,1,2,3), but how can we use a subquery that returns a string, to fill the order field?

@Hogan hi! Thank you for your comment. I'm no sql expert, could you link me to some documentation please? I'll greatly appreciate it! — Alexandre Elshobokshy, May 12 '22 at 15:55
@BillKarwin Find in set returns the index of an item in a list. How does that help to specify the field name to an order by clause? — Hogan, May 12 '22 at 16:12
@Hogan I suppose you mean by using procedures? I've never used any before, I'll try to take a look at how it could be accomplished. Thank you :) — Alexandre Elshobokshy, May 12 '22 at 17:31
so just to be clear -- does the comma separated list specify the order or specify the fields to sort by. Bills answer is using the list to specify the order (that is the first item in the list is the id of the first item returned) where I thought the first item is the list was the column name you want to sort on. My case is the most common (since you are often passed a list of columns to sort on). It is rare you passed a list of the id numbers in order. — Hogan, May 12 '22 at 17:43
@Hogan in my case it is indeed a list of the id numbers to order upon. Maybe my logic in itself is bad. — Alexandre Elshobokshy, May 12 '22 at 17:47
Not really wrong -- just unusual. Bill's answer should work just fine. But there are better ways to do this. I'd recommend storing the ordering information in a table so that you can just join to it to get your order (and with an inner join filter out items you don't have.) This would be the more typical approach. You could also convert those lists to a table first and then join -- which would probably be faster -- depending on a number of things (like the size of your lists) — Hogan, May 12 '22 at 17:53
@Hogan I could've created a table with a simple join, but the logic behind this field is more complex, I can't join directly. If the answer below works, it'd be enough, I think. I'll also try to benchmark and see if the performance is really poorer, I'll think of another way to create the table. Thank you for your comments, really appreciate it! Cheers :) — Alexandre Elshobokshy, May 12 '22 at 18:01

score 1 · Answer 1 · answered May 12 '22 at 17:19

There is no neeed for dynamic sql.

you can doit with FIND_IN_SET

But still this is slower as when you had a normalized Structure.

and fyi you should read Is storing a delimited list in a database column really that bad?

A simplifieed version of your query to demonstarte that it works

CREATE TABLE t1 (Sortder varchar(100), id int)

INSERT INTO t1 VALUES ('1,2,3,4',0),('4,3,2,1',1)

CREATE TABLe t2 (f1 int, val varchar(10))

INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d')

SELECT * FROM t2
ORDER BY
CASE WHEN  4 = 5
THEN  find_in_set( f1, (SELECT Sortder FROM t1 WHERE id = 0) )
ELSE find_in_set( f1, (SELECT Sortder FROM t1 WHERE id = 1) )
END

f1 | val
-: | :--
 4 | d  
 3 | c  
 2 | b  
 1 | a

SELECT * FROM t2
ORDER BY
CASE WHEN  5 = 5
THEN  find_in_set( f1, (SELECT Sortder FROM t1 WHERE id = 0) )
ELSE find_in_set( f1, (SELECT Sortder FROM t1 WHERE id = 1) )
END

f1 | val
-: | :--
 1 | a  
 2 | b  
 3 | c  
 4 | d

db<>fiddle here

score 1 · Answer 2 · answered May 13 '22 at 14:21

The function that you can use to locate a value in a comma separated list of values is FIND_IN_SET().

You don't need the CASE expression and EXISTS:

ORDER BY FIND_IN_SET(
           t.field2,
           COALESCE(
             (SELECT field_order FROM table1 t1 WHERE t1.field1sub = t.field1),
             (SELECT field_order FROM table1 t1 WHERE t1.field1sub = 0)
           )
         )

When there is no row in table1 satisfying the condition of the 1st subquery:

SELECT field_order FROM table1 t1 WHERE t1.field1sub = t.field1

then its result is null in which case COALESCE() will return the result of the 2nd subquery:

SELECT field_order FROM table1 t1 WHERE t1.field1sub = 0

Another way to write the ORDER BY clause would be:

ORDER BY FIND_IN_SET(
           t.field2,
           (
             SELECT field_order 
             FROM table1 t1 
             WHERE t1.field1sub IN (t.field1, 0) 
             ORDER BY t1.field1sub = 0 LIMIT 1
           )
         )

In this case, the subquery:

SELECT field_order 
FROM table1 t1 
WHERE t1.field1sub IN (t.field1, 0) 
ORDER BY t1.field1sub = 0 LIMIT 1

returns the row with t1.field1sub = t.field1 if it exists, but if it does not exist it returns the row with t1.field1sub = 0.

ORDER BY field from a comma separated subquery

2 Answers2