CODEWITHSUNDEEP
mysqlsql
Aleksandr
Aleksandr

Reputation: 91

sql select default order

So I have this sql request (to select products by external ID):

SELECT p.product_id, p.external_id 
FROM oc_product p 
LEFT JOIN oc_product_description pd 
    ON (p.product_id = pd.product_id) 
LEFT JOIN oc_product_to_store p2s 
    ON (p.product_id = p2s.product_id) 
WHERE pd.language_id = '2' 
    AND p.status = '1' 
    AND p.date_available <= NOW()
    AND p2s.store_id = '0' 
    AND p.external_id IN ( 38291, 21472, ... ,10)
LIMIT 0,20

It returns me list of id's:

enter image description here

The order of returned list is ASC. but I want that order would be the same as I wrote external_ids in query. (38291, 21472, 21473, 35229, 35030, ...)

Any idea how to do that?

Upvotes: 1

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The order of a result set is indeterminate unless there is an order by in the outermost query.

The order of a result set is indeterminate unless there is an order by in the outermost query.

The order of a result set is indeterminate unless there is an order by in the outermost query.

Now that we've gotten that out of the way, you can use field() for this purpose:

order by field(p.external_id, 38291,21472,21473,35229,35030,40537,1,46936,46937,46938,46939,46940,46941,13691,37104,11574,11177,27416,15951,11178,27417,25567,37110,34597,5406,5270,37111,30033,9,40301,10)

Alternatively you can find_in_set():

order by find_in_set(p.external_id, '38291,21472,21473,35229,35030,40537,1,46936,46937,46938,46939,46940,46941,13691,37104,11574,11177,27416,15951,11178,27417,25567,37110,34597,5406,5270,37111,30033,9,40301,10')

Upvotes: 1

Related Questions