Page 1 of 1

get rows with order in IN clause

Posted: Fri Dec 18, 2015 4:17 pm
by publicGenome
Hi There,
I'm struggling to get output in the order as provided:

Code: Select all


select app_value from tool_app where tool_app.id in (select id_tool_app from analysis_link where id_pipeline=13) ORDER BY FIND_IN_SET ( tool_app.id, '3,4,2,1');

Correct and desired output
+-------------------+
| app_value |
+-------------------+
| RDP v2 |
| Chimera denovo |
| Split library 300 |
| Split library 250 |
+-------------------+

Code: Select all

select id_tool_app from analysis_link where id_pipeline=13;
This is the tool order:

+-------------+
| id_tool_app |
+-------------+
| 3 |
| 4 |
| 2 |
| 1 |
+-------------+

Code: Select all

select app_value from tool_app where tool_app.id in (select id_tool_app from analysis_link where id_pipeline=13) ORDER BY FIND_IN_SET ( tool_app.id, 'select id_tool_app from analysis_link where id_pipeline=13');
Wrong output
+-------------------+
| app_value |
+-------------------+
| Split library 250 |
| Split library 300 |
| RDP v2 |
| Chimera denovo |
+-------------------+
I'm getting the above Wrong output.

How do I get the correct output in IN Clause to have desired output?

I hope my doubt is clear.

Re: get rows with order in IN clause

Posted: Mon Dec 21, 2015 7:33 am
by publicGenome
gentle bump to this post.

Re: get rows with order in IN clause

Posted: Mon Dec 21, 2015 3:29 pm
by requinix
Assuming MySQL lets you do it (and doesn't require that the second argument be a string literal), you'd need a query that can produce as output a string string in the form "3,4,2,1". GROUP_CONCAT can get you that string.

However what you're doing emulates a JOIN. Instead of FIND_IN_SET and a subquery you should be using a JOIN and then sorting by a field in that table. Which means creating a sort of "sort order" column in that analysis_link table. Which you should do anyways instead of (1) using that subquery in the WHERE and (2) relying on MySQL producing the same order of rows every time.

Code: Select all

+-------------+------------+
| id_tool_app | sort_order |
+-------------+------------+
|           1 |          4 |
|           2 |          3 |
|           3 |          1 |
|           4 |          2 |
+-------------+------------+

Code: Select all

SELECT tool_app.app_value
FROM tool_app
JOIN analysis_link ON tool_app.id = analysis_link.id_tool_app
WHERE analysis_link.id_pipeline = 13
ORDER BY analysis_link.sort_order

Re: get rows with order in IN clause

Posted: Mon Dec 21, 2015 3:37 pm
by publicGenome
requinix wrote:Assuming MySQL lets you do it (and doesn't require that the second argument be a string literal), you'd need a query that can produce as output a string string in the form "3,4,2,1". GROUP_CONCAT can get you that string.

However what you're doing emulates a JOIN. Instead of FIND_IN_SET and a subquery you should be using a JOIN and then sorting by a field in that table. Which means creating a sort of "sort order" column in that analysis_link table. Which you should do anyways instead of (1) using that subquery in the WHERE and (2) relying on MySQL producing the same order of rows every time.

Code: Select all

+-------------+------------+
| id_tool_app | sort_order |
+-------------+------------+
|           1 |          4 |
|           2 |          3 |
|           3 |          1 |
|           4 |          2 |
+-------------+------------+

Code: Select all

SELECT tool_app.app_value
FROM tool_app
JOIN analysis_link ON tool_app.id = analysis_link.id_tool_app
WHERE analysis_link.id_pipeline = 13
ORDER BY analysis_link.sort_order
Hi requinix,

Thanks for your attention to my query.
I ran your query:

Code: Select all

SELECT tool_app.app_value FROM tool_app JOIN analysis_link ON tool_app.id = analysis_link.id_tool_app WHERE analysis_link.id_pipeline = 13 ORDER BY analysis_link.sort_order;
Unfortunately, got an error:
ERROR 1054 (42S22): Unknown column 'analysis_link.sort_order' in 'order clause'
Query runs fine until:

Code: Select all

SELECT tool_app.app_value FROM tool_app JOIN analysis_link ON tool_app.id = analysis_link.id_tool_app WHERE analysis_link.id_pipeline = 13;

Re: get rows with order in IN clause

Posted: Mon Dec 21, 2015 4:36 pm
by requinix
Right, yeah. That "sort_order" represents a column in the table which you can use to decide how to sort the various rows. If you already have something like that then use it, otherwise you'll need to make a column for it.