How to sort all rows, one-per-group in a loop?


  • QA Engineer

    I'd like to sort all rows in a table, one per category in a loop. For example, given the following data:

    +---------+-------------+
    | item_id | category_id |
    +---------+-------------+
    | 4013738 |        1102 |
    | 4016142 |        1102 |
    | 4027380 |        1102 |
    | 4029166 |        1014 |
    | 4031335 |        1125 |
    | 4031984 |        1014 |
    | 4031986 |        1014 |
    | 5034654 |        1123 |
    | 5034656 |        1125 |
    | 5034662 |        1125 |
    | 5034735 |        1109 |
    | 5034736 |        1109 |
    | 5034737 |        1109 |
    | 5040226 |        1123 |
    | 5040227 |        1123 |
    +---------+-------------+
    

    The desired result set should be like this:

    +---------+-------------+
    | item_id | category_id |
    +---------+-------------+
    | 4029166 |        1014 |
    | 4013738 |        1102 |
    | 5034735 |        1109 |
    | 5034654 |        1123 |
    | 4031335 |        1125 |
    | 4031984 |        1014 |
    | 4016142 |        1102 |
    | 5034736 |        1109 |
    | 5040226 |        1123 |
    | 5034656 |        1125 |
    | 4031986 |        1014 |
    | 4027380 |        1102 |
    | 5034737 |        1109 |
    | 5040227 |        1123 |
    | 5034662 |        1125 |
    +---------+-------------+
    

    The item_id in consecutive categories should also be ordered. If a some categories contain more items than others, the cycle should continue the same logic with whatever rows are left.

    This would be a fairly trivial task in a scripting language like php, but I can't for the life of me wrap my head around how to accomplish it in sql.

    Here is a db fiddle with sample data: https://www.db-fiddle.com/f/ioZzvoQfnSNiowe6Rp7QP5/0



  • Using the category as a window, I gave the rows a number within that window, which I used then to order all the rows. In that way, I obtained all the rows that were first within their category, then all that came second, and so on. Remark that I did not order by item_id, so this may not be exactly what you need, but it should get you going.

    with ict as (select ic.*, 
           row_number() over (partition by category_id) rn
           from item_category ic)
    select item_id, category_id
      from ict
      order by ict.rn, ict.category_id;
    

    see https://www.db-fiddle.com/f/ioZzvoQfnSNiowe6Rp7QP5/1




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2