order post according to custom field position

Question

I want a list of posts according to a custom field in Wordpress.

Here I have 9 posts with 3 different meta values (middle, Left Top, Left bottom) and meta key is "position"

here is posts table

Wp_posts:

Post ID    title      Description
1          Post1      -----
2          Post2      -----
3          Post3      -----
4          Post4      ---
5          Post5      ----
6          Post6      --
7          Post7      ----
8          Post8      ---
9          Post9      ----
10         Post10     ---

And here is wp_postmeta table:

meta_id     post_id    Meta_key   meta_value
1           1          position      left Top
2           2          position      Left Bottom 
3           3          position      Left Top
4           4          position      Left Bottom
5           5          position      Middle
6           6          position      Left Bottom
7           7          position      Left Top
8           8          position      Left Bottom
9           9          position      Left top
10          10         position      Middle  

Now I want to get a list according to sorting of position (Middle, Left Bottom, Left Top)

Like this one

post_id    title       meta_value           met_key
5          Post5       Middle               postion
2          Post2       Left Bottom          postion
1          Post1       Left Top             postion
10         Post10      Middle               postion
4          Post4       Left Bottom          postion
3          Post3       Left Top             postion
6          Post6       Left Bottom          postion
7          Post7       Left Top             postion
8          Post8       Left Bottom          postion
9          Post9       Left Top             postion

I am not expert in sql query.

Answer 1

On base of this answer

SELECT post_id, title, meta_value, Meta_key
FROM (
   SELECT post_id, meta_value, Meta_key,
          @grp := IF(@pos = meta_value, @grp + 1,
                     IF(@pos := meta_value, 1, 1)) AS grp
   FROM wp_postmeta
   CROSS JOIN (SELECT @grp := 0, @pos := '') AS vars
   WHERE Meta_key = "position"
   ORDER BY meta_value) AS t
LEFT JOIN Wp_posts 
   ON t.post_id =  Wp_posts.PostID
ORDER BY grp, FIELD(meta_value, 'Middle', 'Left Bottom', 'left Top')   

demo

Answer 2

This should do it:

ORDER BY FIELD(meta_value,'Middle','Left Bottom','Left Top')

More info on the ORDER BY FIELD function: http://www.electrictoolbox.com/mysql-order-specific-field-values/