CODEWITHSUNDEEP
sqlfirebird
NaGeL182
NaGeL182

Reputation: 904

Firebird 1.5 : Group by maximum value

I got the following SQL

SELECT GEP.KOD, GEP.IRSZ, GEP.VAROS, GEP.UTCA, GEP.UGYINT, GEP.EMELET, CIKK.NEV,  
GEPELEM.SZAMLALO FROM GEP LEFT JOIN CIKK ON GEP.CIKK = CIKK.KOD LEFT JOIN GEPELEM ON 
GEPELEM.KOD = GEP.KOD WHERE CEG = 27013
GROUP BY GEPELEM.SZAMLALO, GEP.KOD, GEP.IRSZ, GEP.VAROS, GEP.UTCA, GEP.UGYINT, GEP.EMELET, CIKK.NEV

And i would like to group by GEPELEM.SZAMLALO using its maximum value available...
If i remove the GRoup by statements and only the GEP.ELEM.SZAMLALO remains, it gives an error 

[Error Code: 335544569, SQL State: 42000]  GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

If i use max() function in the SELECT cause it does nothing. the SQL runs, but the results doesn't change
if i use max() in the group by cause and in the select:

[Error Code: 335544569, SQL State: 42000]  GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 2, column 11
max

Same error if i use it only in the Group by cause..

This is firebird 1.5.6 (got no choice to upgrade), and i come from MySQL background.

Upvotes: 0

Views: 590

Answers (1)

M.Ali
M.Ali

Reputation: 69554

Add the where clause in your JOIN condition instead of where clause, Assuming CEG is in GEPELEM table, try the following.....

SELECT GEP.KOD
     , GEP.IRSZ
     , GEP.VAROS
     , GEP.UTCA
     , GEP.UGYINT
     , GEP.EMELET
     , CIKK.NEV
     , MAX(GEPELEM.SZAMLALO) 
FROM GEP 
LEFT JOIN CIKK     ON GEP.CIKK    = CIKK.KOD AND  GEP.CEG = 27013
LEFT JOIN GEPELEM  ON GEPELEM.KOD = GEP.KOD   
GROUP BY GEP.KOD
     , GEP.IRSZ
     , GEP.VAROS
     , GEP.UTCA
     , GEP.UGYINT
     , GEP.EMELET
     , CIKK.NEV

Upvotes: 2

Related Questions