Sql Order By Value

Question

How can I order by a Select by a sequence?

I mean, I have these datas:

column_1   |   column_2
    1      |      a    
    2      |      b    
    3      |      c    
    4      |      d
    4      |      e    
    3      |      f    
    1      |      g    
    2      |      h

And I want to order by 1-4 group, I mean:

column_1   |   column_2
    1      |      a    
    2      |      b    
    3      |      c    
    4      |      d
    1      |      g    
    2      |      h 
    3      |      f    
    4      |      e

I would like column_1 ordered by 1,2,3,4 sequence of values then 1,2,3,4 again.
I mean, I don't want ordering this way: 1,1,2,2,3,3,4,4...

[[EDIT]]
The column_2 should by Asc, I mean: Order By column_2 Asc

[[EDIT 2]]
I will use this Select on SQLite. I'm trying to find a way using just SQL commons commands.

Sql Fiddle

Does anyone know how it's possible?

Is there anything to know which 2 should come after a specific 1? Or does that matter? — crthompson, Sep 07 '14 at 18:41
if you want 1,1,2,2 then `group by`. If you also want ordering then `group by col_1, col_2 order by col_1, col_2 asc` — radarbob, Sep 07 '14 at 18:53
I don't want 1,1,2,2. I want: 1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4,1,2,3,4... — Felipe M, Sep 07 '14 at 18:56

score 1 · Answer 1 · answered Sep 07 '14 at 20:39

If you want to order by a sequence, you really want to enumerate each value in column 1, independently for each value. For example:

I'm not sure if there is an easy way to do this in SQLite. In MySQL, you would use variables. In other databases, window functions. You can accomplish this correlated subqueries:

select column_1, column_2
from (select t.*,
             (select count(*) from table t2 where t2.column_1 = t.column_1 and t2.rowid <= t.rowid
             ) as seqnum
      from table t
     ) t
order by seqnum, column_1;

Sql Order By Value

Sql Fiddle

1 Answers1