Keeping ordering in a WHERE IN clause in my SQL

Question

I am running the following SQL query:

SELECT * FROM cms_albums WHERE id IN (SELECT album_id FROM cms_albums_collections WHERE collection_id = 1 ORDER BY position ASC)

Now, assume the inner query

SELECT album_id FROM cms_albums_collections WHERE collection_id = 1 ORDER BY position ASC

returns the following:

album_id
"4"
"2"

This is the order that I want. However the overall query will return:

id   name   
"2" "Second album"
"4" "First album"

I am assuming this is because the entries are in this order in the 'cms_albums' table. Is there any way I can get my overall result in the order given by the inner query?

Thanks

Answer 1

Try this variation:

SELECT  DISTINCT c.*
FROM    cms_albums c
JOIN    cms_albums_collections cc
ON      c.id = cc.album_id
WHERE   cc.collection_id = 1
ORDER BY
        cc.position ASC

The order of the items in an IN list is lost, instead form a join, then you can apply the ordering you wanted.

Answer 2

I don't agree with the statement that the results that were returned were necessarily the order of the table or the order in which the rows were inserted into the table. If the execution plan that was used by the server used a non-clustered index or an in-memory set of data, then the sort order won't necessarily reflect the order of the actual table or the order in which the data was inserted. Having said that, there are a few options that may achieve your goal:

1. Instead of using an IN statement you could use a JOIN and then sort by the clustered index of the secondary table.
2. Modified verison of 1. If the sub query returns more than one row per albumn id then aggregate the data in either the CTE or the sub query. You will still need to sort on the clustered index.