sql select rows based on column having duplicate values but order them distinctly

Question

I have a Table with these two columns

name  |  position
_______________
bob   |   1
rob   |   1
sam   |   1
tom   |   2
shyam |   2
ram   |   2
shah  |   3
sara  |   3
shiv  |   3
rogue |   4
logan |   4
xavier|   4

I need to order by distinct position but the duplicates should not be removed, the final result must be like

    name  | position
    _______________
    bob   |   1
    tom   |   2
    shah  |   3
    rogue |   4
    rob   |   1
    shyam |   2
    sara  |   3
    logan |   4
    sam   |   1
    ram   |   2
    shiv  |   3
    xavier|   4

I tried both distinct position as well as group by position, but both remove the duplicates but i want them to be grouped as sets of rows such that the first copy occurs then the rows of second duplicate occur etc.Any suggestions or help on achieving it ?

Gordon Linoff · Accepted Answer · 2013-07-03 01:33:23Z

1

You need to enumerate the rows within each value of position. Then you can use this for ordering:

select t.name, t.position
from (select t.*, if(@prev = position, @rn := @rn + 1, 1) as rn,
             @prev := position
      from t cross join
           (select @rn := 0, @prev := -1)
      order by position
     ) t
order by rn

answered Jul 3, 2013 at 1:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

sql select rows based on column having duplicate values but order them distinctly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related