0

I was trying to order a result set by the order of the values in an IN() clause.

SELECT * FROM CrossReference WHERE cross_reference_id IN (SELECT Id FROM FilteredIds)

So I tried to find a function such as MySql FIELD(). Then I found these answers (answer1, answer2) which explain how to do the exact thing on SQLite using the INSTR().

SELECT *, INSTR(',GDBR10,GDBR5,GDBR30,', ',' || ticker || ',') POS
FROM tbl
WHERE POS>0
ORDER BY POS;

So it's working as expected, but I want to populate the ids dynamically using a select query. I tried many approaches, but nothing seemed to work. Here is the last one I tried. It gave me just one result row (a result related to the first filterId).

SELECT *, INSTR (','||(SELECT id FROM FilteredIds)||',', ',' || cross_reference_id || ',') POS FROM CrossReference WHERE POS>0 ORDER BY POS;

So I guess I'm making some kind of mistake when concatenating the SELECT query with the rest of the code. Because when I manually enter the filtered Ids it works and returns results according to the entered filter ids.

Kamal Thennakoon
  • 1,439
  • 1
  • 7
  • 8
  • 1
    You are using `POS` in the ORDER BY clause which means that the order of the ids returned by the subquery is important, right? Is this order the same as ORDER BY id? – forpas Mar 23 '22 at 21:06
  • Yes, it's important. I want to order the CrossReference result set according to the order of filterIds. However, I don't get the second part of your question. What do you mean? Could you elobrate, please? – Kamal Thennakoon Mar 23 '22 at 21:24
  • `SELECT id FROM FilteredIds` does not return the ids in any specific order. Do you want them concatenated in a specific order? – forpas Mar 23 '22 at 21:26
  • Ahh no forpas, The filteredIds table's records have already been ordered. I mean, I want to maintain the insertion order of the filteredIds. – Kamal Thennakoon Mar 23 '22 at 21:37
  • 1
    This means that you are using some kind of ORDER BY clause inside the subquery which you did not include in the code you posted, but it is useless outside the subquery as you already found out because the subquery as it is used returns only 1 row. Post sample data and expected results to clarify what you want. – forpas Mar 23 '22 at 21:42
  • Forpas, I just want to itterate the filteredIds inside the INSTR() function. when I do it manually, It works and returns results rows accordingly. as an example If I manually enter 5 correct filter ids, It will return 5 result rows. but when I do it using the select query, which has more than 120 ids, only returns the result related to the first id. So I guess I'm making some kind of mistake when concatenate the select query with the rest of the code. Forpas I think now you got a clear idea of my problem. – Kamal Thennakoon Mar 23 '22 at 21:55

0 Answers0