CODEWITHSUNDEEP
sqlruby-on-railspostgresqlanalytic-functions
kburkhardt
kburkhardt

Reputation: 153

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

Upvotes: 2

Views: 80

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

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;

Upvotes: 2

Related Questions