CODEWITHSUNDEEP
sqlruby-on-railspostgresql
goddamnyouryan
goddamnyouryan

Reputation: 6896

Mixed ordering by date and by type

So let's say I have a database that looks like this

Posts
id | author_id | created_at
 1           1    1.day.ago
 2           1    2.day.ago
 3           2    3.day.ago
 4           1    4.day.ago

Authors
id | type
 1   Male
 2   Female

I want to do a query on these posts so that they return in desc order of created, but also going through all the types.

So if I just did:

Post.order('created_at desc')

I would get:

Posts
id | author_id | created_at
 1           1    1.day.ago
 2           1    2.day.ago
 3           2    3.day.ago
 4           1    4.day.ago

But let's say instead I want the results to be in this order:

Posts
id | author_id | created_at
 1           1    1.day.ago
 3           2    3.day.ago
 2           1    2.day.ago
 4           1    4.day.ago

So that I get first the most recent post from author 1, then the most recent post from author 2, even if it's older than the next post from author 1, then, since there are no more posts from author 2, it returns the rest in normal created_at order.

Let's assume there will never be more than 20 authors or so, but I always want to cycle through the most recent posts from each other, then the next most recent one from each, etc.

Is this possible to do in sql?

Upvotes: 1

Views: 57

Answers (3)

HoneyBadger
HoneyBadger

Reputation: 15140

You can order by a custom order using something like:

ORDER BY ROW_NUMBER()OVER(PARTITION BY author_id ORDER BY created_at DESC)
,        author_id

If you add it to the select, you can see the result more easy, and apply tweaks where necessary.

At least, this is allowed in sql server, I assume likewise for postgreSQL.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

We can assign a row number across a partition on authors, with the most recent posts being assigned the lowest row numbers. Then order by this row number, followed by the author id to break the tie.

select
    t.id,
    t.author_id,
    t.created_at
from
(
    select id, author_id, created_at,
        row_number() over (partition by author_id order by created_at desc) rn
    from Posts
) t
order by
    t.rn,
    t.author_id

Upvotes: 1

Oto Shavadze
Oto Shavadze

Reputation: 42753

If I correctly understand, one way is:

with Posts(id , author_id , created_at) as(
select 1       ,    1    ,'2017-09-17'::date union all
select 2       ,    1    ,'2017-09-16'::date union all
select 3       ,    2    ,'2017-09-15'::date union all
select 5       ,    2    ,'2017-09-10'::date union all
select 4       ,    1    ,'2017-09-14'::date  
)

select * from (
    select * ,row_number() over(partition by author_id order by created_at desc) as rn  
    from Posts
) t
order by rn, created_at desc

Upvotes: 1

Related Questions