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

Question

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!

i know u said not using order by but order by rand() should work — Liam Allan
– Liam Allan, Commented Dec 19, 2011 at 20:38

Joe Stefanelli · Accepted Answer · 2011-12-19 20:37:11Z

3

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

answered Dec 19, 2011 at 20:37

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ben English · Accepted Answer · 2011-12-19 20:39:22Z

0

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.

answered Dec 19, 2011 at 20:39

Ben English

3,9382 gold badges26 silver badges33 bronze badges

Comments

Callie J · Accepted Answer · 2011-12-19 20:49:00Z

0

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

answered Dec 19, 2011 at 20:49

Callie J

31.4k6 gold badges74 silver badges114 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related