0

I have a table that has records attached to owners ids.

when I retrieve a result-set it is likely to have multiple rows belonging to the same owner.

I would like to order my results in a fair fashion to the owners showing 1 record for each owner and then repeating that process for their next records.

I have tried using order by FIELD('owner',{list,of,owners}) but this still groups all the records together showing all of one owners results before others.

Example: 

Record | Owner | value
1      | A     | xyz
2      | A     | xyz
3      | B     | xyz
4      | C     | xyz
5      | C     | xyz
6      | B     | xyz

I would like this result set to be in the order of 

Record | Owner
1      | A
3      | B
4      | C
2      | A
6      | B
5      | C

I am using Zend_Db_Table for my queries and cannot change. Therefore PHP / Mysqli based code please.

The order changes but is always in a pattern. I have access to the complete pattern at anytime telling me the order of the ids of the owners.

Can I achieve this without resorting to resorting the result set in PHP?

davidofadelaide
  • 41
  • 1
  • 4

1 Answers1

1

Something like this will work..

  1. Create new column called rank to assign a rank for each row. The value of this column will be the rank grouped by owner.
  2. Select columns needed from the output table of step 1 and order it by rank.

DEMO

SELECT report,owner FROM (
        SELECT report,owner,value, IF(@prev <> owner, @cnt := 1, @cnt := @cnt + 1) AS rank, @prev := owner
        FROM x, (SELECT @prev := null, @cnt := 0) vars  
        ORDER BY owner
    ) as y ORDER BY y.rank,y.owner;

NOTE: replace x with table name

Sampath Liyanage
  • 4,776
  • 2
  • 28
  • 40