order post according to custom array position

Question

I want to list of post according to custom filed.

Here i have 9 post with different 3 position (middle, top, bottom)

Post ID    title      position
1          Post1      Top
2          Post2      Bottom 
3          Post3      Top
4          Post4      Bottom 
5          Post5      Middle
6          Post6      Bottom 
7          Post7      Top
8          Post8      Bottom 
9          Post9      Top
10         Post10     Middle

Now i want to get post according like this order

5          Post5      Middle
2          Post2      Bottom 
1          Post1      Top
10         Post10     Middle
4          Post4      Bottom
3          Post3      Top
6          Post6      Bottom
7          Post7      Top
8          Post8      Bottom
9          Post9      Top  

So first came middle,bottom,top so i want post order like this one, middle,bottom and top. I do not want complete middle position and then bottom and then top.

I like to list get first 3 post of middle,bottom, top.I need this listing wordpress, can any one know, how can i do this task

Thanks CSR

Answer 1

You can use a query like the following:

SELECT PostID, title, position
FROM (
   SELECT PostID, title, position,
          @grp := IF(@pos = position, @grp + 1,
                     IF(@pos := position, 1, 1)) AS grp
   FROM mytable
   CROSS JOIN (SELECT @grp := 0, @pos := '') AS vars
   ORDER BY position, title) AS t
ORDER BY grp, FIELD(position, 'Middle', 'Bottom', 'Top')   

Demo here