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.

  1. i believe distinct on needs first.
  2. i don't know how order results of max function
  3. 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

Popular posts from this blog

c# - SharpSsh Command Execution -

python - Specify path of savefig with pylab or matplotlib -

How to run C# code using mono without Xamarin in Android? -