how to use DISTINCT ON with mysql using ActiveRecord

Question

Want all the latest visit of all the distinct user.

For this I am using below query

Event.order(time: :desc).select('DISTINCT ON(user_id) user_id, time')

Getting SQL syntax error.

ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON(user_id) user_id, time FROM `events` ORDER BY `events`.`time` DESC ' at line 1: SELECT  DISTINCT ON(user_id) user_id, time FROM `events` ORDER BY `events`.`time` DESC LIMIT 11)

events table column names

["id", "visit_id", "user_id", "name", "properties", "time"]

can anyone help me out in this

Expected output something like this

{ 21 => "2018-12-18 09:44:59", 42 => "2018-12-19 12:08:59"}

21 is user_id and value is last visit time

Using mysql as database

Possible duplicate of [How to use SELECT DISTINCT ON with MySQL and Rails](https://stackoverflow.com/questions/26894868/how-to-use-select-distinct-on-with-mysql-and-rails) — Nick, Dec 20 '18 at 12:41
`DISTINCT ON(columns). *` is PostgreSQL syntax but you can workaround it in MySQL with [SUBSTRING_INDEX(GROUP_CONCAT(..))](https://www.db-fiddle.com/f/ioddCbTbeHgTKcCeHHzLpL/1) syntax — Raymond Nijland, Dec 20 '18 at 13:16

Raymond Nijland · Answer 1 · 2019-07-09T17:27:07.470

Like i said in the comments DISTINCT ON(column), * is PostgreSQL SQL syntax.

The query below you need to have to rewrite in MySQL

SELECT 
 DISTINCT ON(user_id) AS user_id, time
FROM 
 <table>
ORDER BY 
 time DESC
LIMIT 11

The most easy to do that is using SUBSTRING_INDEX(GROUP_CONCAT(..)).
The queries below should give you the correct results.

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT 
   user_id
 , SUBSTRING_INDEX(
     GROUP_CONCAT(time
                  ORDER BY
                     time DESC
     )
     , ','
     , 1
 ) AS time
FROM 
 <table>
GROUP BY 
 user_id
ORDER BY 
 time DESC
LIMIT 11

score 1 · Accepted Answer · answered Dec 21 '18 at 06:12

So you want all user visits with last visited time.

Instead of use DISTINCT function, you can use GROUP with MAX function.

Query looks like

Events.group(:user_id).maximum(:time)

This Outputs your desired results

{21=>Tue, 18 Dec 2018 11:15:24 UTC +00:00, 23=>Thu, 20 Dec 2018 06:42:10 UTC +00:00}

Hope this works for you.

FYI DISTINCT ON(columns). is PostgreSQL Syntax.

how to use DISTINCT ON with mysql using ActiveRecord

2 Answers2

Linked