CODEWITHSUNDEEP
mysqlsql
kadalamittai
kadalamittai

Reputation: 2166

Complicated order By - Suborder by Query

I need to perform a summary calculation for the below mentioned table structure for i need to order by "sessionId" and subgroup by the "sequenceId". Particular session can have one or more sequence. each sequence starts with one and move ahead sequencailly. primary keys and flow-squence may not be in tandem.

table >>

pk_id  session-id    sequence   some_other columns
  1    AAAAAAAA          1        blah-blah-blah
  2    AAAAAAAA          2        blah-blah-blah 
  3    AAAAAAAA          3        blah-blah-blah
  4    AAAAAAAA          2        blah-blah-blah
  5    AAAAAAAA          1        blah-blah-blah
  6    AAAAAAAA          3        blah-blah-blah
  7    AAAAAAAA          3        blah-blah-blah
  8    AAAAAAAA          2        blah-blah-blah
  9    AAAAAAAA          1        blah-blah-blah

I need to order by

 pk_id  session-id    sequence   some_other columns
  1    AAAAAAAA          1        blah-blah-blah
  2    AAAAAAAA          2        blah-blah-blah 
  3    AAAAAAAA          3        blah-blah-blah

  5    AAAAAAAA          1        blah-blah-blah
  4    AAAAAAAA          2        blah-blah-blah
  6    AAAAAAAA          3        blah-blah-blah

  9    AAAAAAAA          1        blah-blah-blah
  8    AAAAAAAA          2        blah-blah-blah
  7    AAAAAAAA          3        blah-blah-blah

Any help would be appreciated.

Upvotes: 1

Views: 1315

Answers (1)

Andriy M
Andriy M

Reputation: 77667

Assuming you want to group together the first sequence=1 with the first sequence=2 and the first sequence=3, and similarly the second 1 with the second 2 and second 3 and so on (using pk_id as the order), you could use variable assignment to number sequence values, then use the resulting numbers for sorting.

This is what I mean:

SELECT
  pk_id,
  session_id,
  sequence,
  some_other_column
FROM (
  SELECT
    @row := (session_id = @sid AND sequence = @seq) * @row + 1 AS row,
    pk_id,
    @sid := session_id AS session_id,
    @seq := sequence AS sequence,
    some_other_column
  FROM
    atable,
    (SELECT @row := 0, @sid := '', @seq := 0) AS s
  ORDER BY
    session_id,
    sequence,
    pk_id
) AS s
ORDER BY
  session_id,
  row,
  sequence
;

This query can be tested at SQL Fiddle.

Upvotes: 3

Related Questions