mysql - DISTINCT ON query w/ ORDER BY max value of a column -
i've been tasked converting rails app mysql postgres asap , ran small issue.
active record query:
current_user.profile_visits.limit(6).order("created_at desc").where("created_at > ? , visitor_id <> ?", 2.months.ago, current_user.id).distinct
produces sql:
select visitor_id, max(created_at) created_at, distinct on (visitor_id) * "profile_visits" "profile_visits"."social_user_id" = 21 , (created_at > '2015-02-01 17:17:01.826897' , visitor_id <> 21) order created_at desc, id desc limit 6
i'm pretty confident when working mysql i'm new postgres. think query failing multiple reasons.
- i believe distinct on needs first.
- i don't know how order results of max function
- can use max function this?
the high level goal of query return 6 recent profile views of user. pointers on how fix activerecord query (or it's resulting sql) appreciated.
the high level goal of query return 6 recent profile views of user.
that simple. don't need max()
nor distinct
this:
select * profile_visits social_user_id = 21 , created_at > (now() - interval '2 months') , visitor_id <> 21 -- ?? order created_at desc nulls last, id desc nulls last limit 6;
i suspect question incomplete. if want:
the 6 latest visitors latest visit page
need subquery. cannot sort order in 1 query level, neither distinct on
, nor window functions:
select * ( select distinct on (visitor_id) * profile_visits social_user_id = 21 , created_at > (now() - interval '2 months') , visitor_id <> 21 -- ?? order visitor_id, created_at desc nulls last, id desc nulls last ) sub order created_at desc nulls last, id desc nulls last limit 6;
the subquery sub
gets latest visit per user (but not older 2 months , not visitor21
. order by
must have same leading columns distinct on
.
you need outer query 6 latest visitors then.
consider sequence of events:
why nulls last
? sure, did not provide table definition.
Comments
Post a Comment