Search code examples
sqlruby-on-railspostgresqlanalytic-functions

SQL sort that distributes results


Given a table of products like this:

ID Name     Seller ID  Updated at
-- ----     ---------  ----------
1  First      3         2012-01-01 12:00:10
2  Second     3         2012-01-01 12:00:09
3  Third      4         2012-01-01 12:00:08
4  Fourth     4         2012-01-01 12:00:07
5  Fifth      5         2012-01-01 12:00:06

I want to construct a query to sort the products like this:

ID
---
1
3
5
2
4

In other words, the query should show most recently updated products, distributed by seller to minimize the likelihood of continuous sequences of products from the same seller.

Any ideas on how to best accomplish this? (Note that the code for this application is Ruby, but I'd like to do this in pure SQL if possible).

EDIT:

Note that the query should handle this case, too:

ID Name     Seller ID  Updated at
-- ----     ---------  ----------
1  First      3         2012-01-01 12:00:06
2  Second     3         2012-01-01 12:00:07
3  Third      4         2012-01-01 12:00:08
4  Fourth     4         2012-01-01 12:00:09
5  Fifth      5         2012-01-01 12:00:10

to produce the following results:

ID
---
5
4
2
3
1

Solution

  • One option demonstrated in this sqlfiddle is

    select subq.*
      from (
        select rank() over (partition by seller_id order by updated_at desc) rnk,
               p.*
          from products p) subq
     order by rnk, updated_at desc;