CODEWITHSUNDEEP
mysqlsql-order-by
PaperChase
PaperChase

Reputation: 1585

Pull rows from database in certain order (not using ORDER BY)

Basically I have a list of merchantid's 18,36,90. I want to pull all rows with these merchantid's.

In the first case I want pull the rows with the merchantid's in this order 18,36,90. The following MYSQL statement pulls them in the correct order because coincidentally the merchantid's or in ascending order:

SELECT * FROM tblMerchants WHERE merchantid=18 OR merchantid=36 OR merchantid=90

What if I want to pull the rows with the merchantid's in a different order like 36,90,18 that isn't ascending or descending? Thanks!

Upvotes: 2

Views: 74

Answers (3)

Callie J
Callie J

Reputation: 31306

If there isn't any other column in the SELECT you can use, you can add one in and do something like this (it's a variation on Joe's answer).

SELECT *, CASE
      WHEN merchantid = 36 THEN 1
      WHEN merchantid = 90 THEN 2
      WHEN merchantid = 18 THEN 3
    END AS myorder 
  FROM tblMerchants WHERE last_name IN (18, 36, 90)
  ORDER BY myorder

Upvotes: 0

Ben English
Ben English

Reputation: 3918

If you want your resultset to be returned in a different order then the default then you'll have to use the ORDER BY clause. You can order by a random value to get a random order or you can define your own order with a 'dummy' column where you assign the values arbitrarily and then order resultset by that column.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

Never assume that your data will be returned in a certain order (as you state that your results are currently returned by ascending merchantid) unless you have an explicit ORDER BY in your query.

For your purposes here, you could hard code your desired order with a CASE statement.

SELECT *
    FROM tblMerchants
    WHERE merchantid IN (18, 36, 90)
    ORDER BY CASE WHEN merchantid=36 THEN 1
                  WHEN merchantid=90 THEN 2
                  WHEN merchantid=18 THEN 3
                  ELSE 4
             END

Upvotes: 3

Related Questions