Getting duplicate rows while using DISTINCT in mysql database-CodePudding

SELECT distinct user_id as "User Id",
       date(date_created) AS "time",
FROM user
WHERE date_created BETWEEN FROM_UNIXTIME(1630141526) AND FROM_UNIXTIME(1646039126)
  AND status = 'CONFIRMED'
  AND organization_uuid = '// organization id';

This is my sql query I use the distinct for removing distinct user id but my query return duplicates user id.

I am getting user id = 139555 two times even i use distinct keyword

CodePudding user response：

I think you might want to use an aggregate function instead of DISTINCT, if you want to get minimum per user_id you can try to use MIN otherwise use MAX to get maximum per user_id.

SELECT user_id as "User Id",
       MAX(date(date_created)) AS "time"
FROM user
WHERE date_created BETWEEN FROM_UNIXTIME(1630141526) AND FROM_UNIXTIME(1646039126)
  AND status = 'CONFIRMED'
  AND organization_uuid = '// organization id'
GROUP BY user_id