get rows with order in IN clause

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
publicGenome
Forum Contributor
Posts: 110
Joined: Thu Apr 16, 2015 7:55 am

get rows with order in IN clause

Post 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.
publicGenome
Forum Contributor
Posts: 110
Joined: Thu Apr 16, 2015 7:55 am

Re: get rows with order in IN clause

Post by publicGenome »

gentle bump to this post.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: get rows with order in IN clause

Post 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
publicGenome
Forum Contributor
Posts: 110
Joined: Thu Apr 16, 2015 7:55 am

Re: get rows with order in IN clause

Post 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;
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: get rows with order in IN clause

Post 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.
Post Reply