DS9 DS9 - 2 months ago 5
MySQL Question

How to fetch data in simultaneous order in Mysql

I have table like below:

id----image----type

1-----a.png----1

2-----b.png----1

3-----c.png----2

4-----d.png----1

5-----e.png----2

Now i want to fetch data in below order (first type 1 then type 2 then type 1 and so on):

1-----a.png----1

3-----c.png----2

2-----b.png----1

5-----e.png----2

4-----d.png----1

So how to achieve that?

I have below query so far:


SELECT *
FROM
table

WHERE (TYPE =1
OR TYPE =2)
ORDER BY TYPE

Answer

E.g

Select x.* from mytable x 
join mytable y on y.type =x.type and y.id <= x.id 
group by x.id 
order by count(*),x.type

or faster - and what I think @1000111 was trying to get at...

SELECT x.id
     , x.image
     , x.type
  FROM
      ( SELECT a.*
             , CASE WHEN @prev = type THEN @i:=@i+1 ELSE @i:=1 END rank 
             , @prev := type
          FROM your_table a
             , (SELECT @i:=0, @prev:=null) vars
         ORDER 
            BY type
             , id
      ) x
  ORDER  
     BY rank
      , type;

http://sqlfiddle.com/#!9/3e27c/8

Comments