2

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
kburkhardt
  • 153
  • 1
  • 5
  • @Ben I'm using Postgres. Thanks. – kburkhardt Dec 03 '12 at 22:44
  • 1
    Next question :-). Imagine that you had an addition four rows for seller 3 , with IDs 6-9 and the dates going in the same order you have. Is the order now 1, 3, 5, 2, 4, 6, 7, 8, 9, i.e. what happens to the tail end when a few sellers have many more items than everyone else. – Ben Dec 03 '12 at 22:48
  • Yes, you have the correct order. In that scenario, seller 3's oldest products would be sorted to the end of the result set. – kburkhardt Dec 03 '12 at 22:58
  • Just wondering what happens when a seller doesn't update their products for a long time. In your second example imagine Seller 5 last updated their product on 2011-12-01. Should that still rank above product 2 and 4? – Greg Dec 04 '12 at 06:01
  • @kburkhardt sorry, I got my numbers wrong. If Seller 5 last updated their product on 2011-12-01, should the answer be 4,2,5,3,1? If so I think that's what Justin Cave's answer will give you. – Greg Dec 04 '12 at 23:50
  • @Greg Yes, you're correct. Justin Cave's answer solves it. – kburkhardt Dec 05 '12 at 02:04

1 Answers1

2

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;
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • The above query does generate the original output that I specified but that seems to be due to the way I originally ordered the data. Please see the update where I've put the timestamps in reverse order. In this case, this query is ordering by seller_id first ... BUT I'm looking for it to sort by updated_at first (ie. really want the newest items first). – kburkhardt Dec 04 '12 at 02:25
  • @kburkhardt - Updated again – Justin Cave Dec 04 '12 at 04:31