CODEWITHSUNDEEP
mysqlsql-order-bydistinct
hsemarap
hsemarap

Reputation: 227

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

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 ?

Upvotes: 0

Views: 207

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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

Upvotes: 1

Related Questions