VespaQQ VespaQQ - 6 months ago 17
PHP Question

Oracle GROUP BY doesn't work as expected

I got SQL query like this:

SELECT Z.ID,
Z.ZADAVATEL,
Z.DATUM_ZADANI,
Z.LIN_OBL,
Z.DRUH_ZMENY,
Z.CISLO_ZMENY,
Z.PRILOHY
FROM ZMENY Z
JOIN ZMENY_DILY ZD
ON (ZD.ID_ZMENY=Z.ID)
WHERE 1=1 %sql
ORDER BY Z.ID DESC %lmt%ofs


Table ZMENY has 9 rows, ZMENY_DILY has 11 rows. The problem is I get 11 rows as a result but 2 of them are duplicate rows and I'm trying to get rid off these two duplicates, I tried to use

GROUP BY Z.ID


unfortunately Oracle says: ORA-00979: not a GROUP BY expression

I'm not much into Oracle, MySQL wouldn't have any problem I guess ... any tips ?

Answer

You don't need group by in this case you can use select distinct

SELECT distinct  
    Z.ID, 
    Z.ZADAVATEL, 
    Z.DATUM_ZADANI,
    Z.LIN_OBL,
    Z.DRUH_ZMENY,
    Z.CISLO_ZMENY,
    Z.PRILOHY 
FROM ZMENY Z JOIN ZMENY_DILY ZD ON (ZD.ID_ZMENY=Z.ID)
ORDER BY Z.ID DESC %lmt%ofs