0

When I run the following SQL against MySQL 8.0.21

SELECT DISTINCT
     a.id_org
FROM audit a
WHERE a.id_user = 2
    AND a.cd_action = 'VIEWED'
    AND a.id_service_provider IS NOT NULL
ORDER BY a.dt_created desc    
LIMIT 2

I get the results 573, and 59

SELECT 
     a.id_org
FROM audit a
WHERE a.id_user = 2
    AND a.cd_action = 'VIEWED' 
    AND a.id_service_provider IS NOT NULL
ORDER BY a.dt_created desc    
LIMIT 2

I get the results 194, and 194. 194 is actually my expected first ID, but only want a distinct list.

My Question is: Why would a DISTINCT alter the results in such a way? Having the distinct does NOT return the most recent results as per the order by. Without the DISTINCT returns the right data but multiple entries. Must be missing something obvious here.

Thank you.

Jeremy
  • 11
  • 1
  • Please try to order it by row_number and then see the result. I am unable yo reproduce the same on MYSQL 8, Could you please provide some test data. – Atif Aug 21 '20 at 09:58
  • @RiggsFolly this is not a bug. The OP is using distinct and order by in a way that those are not supposed to be used. MS SQL Server would actually raise an error message for this query and rightly so. – Shadow Aug 21 '20 at 10:00

0 Answers0