DISTINCT ON query w/ ORDER BY max value of a column

Question

I've been tasked with converting a Rails app from MySQL to Postgres asap and ran into a small issue.
The active record query:

current_user.profile_visits.limit(6).order("created_at DESC").where("created_at > ? AND visitor_id <> ?", 2.months.ago, current_user.id).distinct

Produces the SQL:

SELECT  visitor_id, MAX(created_at) as created_at, distinct on (visitor_id) *
FROM "profile_visits"
WHERE "profile_visits"."social_user_id" = 21
AND (created_at > '2015-02-01 17:17:01.826897' AND visitor_id <> 21)
ORDER BY created_at DESC, id DESC
LIMIT 6

I'm pretty confident when working with MySQL but I'm honestly new to Postgres. I think this query is failing for multiple reasons.

I believe the distinct on needs to be first.
I don't know how to order by the results of max function
Can I even use the max function like this?

The high level goal of this query is to return the 6 most recent profile views of a user. Any pointers on how to fix this ActiveRecord query (or it's resulting SQL) would be greatly appreciated.

The query in question will not work with MySQL. `distinct on` is a Postgres construct, so I assume the whole thing is Postgres-compatible. However, the use of `max()` is highly suspect. — Gordon Linoff, Apr 01 '15 at 16:38
As always, your version of Postgres and the table definition should be in the question. Also, the description of what the query is supposed to do is probably incomplete. — Erwin Brandstetter, Apr 02 '15 at 20:44

score 0 · Accepted Answer · edited May 23 '17 at 12:06

The high level goal of this query is to return the 6 most recent profile views of a user.

That would be simple. You don't need max() nor DISTINCT for this:

SELECT *
FROM   profile_visits
WHERE  social_user_id = 21
AND    created_at > (now() - interval '2 months')
AND    visitor_id <> 21  -- ??
ORDER  BY created_at DESC NULLS LAST, id DESC NULLS LAST
LIMIT  6;

I suspect your question is incomplete. If you want:
the 6 latest visitors with their latest visit to the page
then you need a subquery. You cannot get this sort order in one query level, neither with DISTINCT ON, nor with window functions:

SELECT *
FROM  (
   SELECT DISTINCT ON (visitor_id) *
   FROM   profile_visits
   WHERE  social_user_id = 21
   AND    created_at > (now() - interval '2 months')
   AND    visitor_id <> 21  -- ??
   ORDER  BY visitor_id, created_at DESC NULLS LAST, id DESC NULLS LAST
   ) sub
ORDER  BY created_at DESC NULLS LAST, id DESC NULLS LAST
LIMIT  6;

The subquery sub gets the latest visit per user (but not older than two months and not for a certain visitor21. ORDER BY must have the same leading columns as DISTINCT ON.

You need the outer query to get the 6 latest visitors then.
Consider the sequence of events:

Best way to get result count before LIMIT was applied

Why NULLS LAST? To be sure, you did not provide the table definition.

PostgreSQL sort by datetime asc, null first?

DISTINCT ON query w/ ORDER BY max value of a column

1 Answers1

Linked