CODEWITHSUNDEEP
phpsqloracle-database
Bezdutchek
Bezdutchek

Reputation: 87

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 ?

Upvotes: 1

Views: 267

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You can use select distinct, assuming all columns are duplicates:

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
WHERE  1=1 -- %sql ???
ORDER BY Z.ID DESC;

Alternatively, you can use IN:

SELECT z.*
FROM ZMENY Z
WHERE Z.ID IN (SELECT ID_ZMENY FROM ZMENY_DILY ZD)
ORDER BY Z.ID DESC;

Upvotes: 0

Related Questions