mysql - Same query different database different result

Question

I try to change database from 'MySQL' to 'PostgreSQL' but i have problem some of my query

SELECT
    SUM( CASE WHEN b.DISPLAYNAME IS NULL THEN 0 ELSE 1 END ) AS NUMMENUDETAIL,
    A.DISPLAYNAME AS GROUPNAME,
    b.displayname,
    D.NAME,
    min( A.ROLEID ) AS ROLEID 
FROM
    usermenu A
    LEFT JOIN usermenu B ON A.ID = B.GROUPID,
    userrole C,
    position D,
    positiondetail E 
WHERE
    ( A.GROUPID IS NULL ) 
    AND D.id = E.position_id 
    AND A.ROLEID = E.userrole_id 
    AND A.ROLEID = C.ID 
    AND B.ROLEID = E.userrole_id 
    AND B.ROLEID = C.ID 
    AND b.displayname = 'Transaction Listing Report' 
GROUP BY
    A.DISPLAYNAME,
    b.displayname 
    LIMIT 10

i use this query into "MySQL" it pass but when i use in "PostgreSQL" it error, They want my to GROUP BY d.name then if i GROUP BY it may have different result How can i fix this ?

question from:https://stackoverflow.com/questions/65844912/same-query-different-database-different-result

Answer 1

This query does not comply with the SQL standard, so it is unsurprising that it does not work on all databases. The standard requires that all columns in the SELECT list that do not appear only inside aggregate functions must be in the GROUP BY clause.

You can have the same effect by using the (again, non-standard) DISTINCT ON clause in PostgreSQL.