How to get the maxvalue of the following query

Question

I have this query and i need to get the maxvalue of the CCSEQ camp, i have tried using this query but it doesn´t work, if anyone can help me i will be very grateful.

the query

select max(cc.CCSEQ), cc.ccline4, cc.ccstreetno, cccity
from ccontact cc
where customer_id = '724609' and ccbill = 'X';

EDIT

I have resolved the issue with this query

select cc.ccline4, cc.ccstreetno, cccity
from ccontact cc
where CC.customer_id = '724609' and CC.ccbill = 'X'
AND cc.CCSEQ = (SELECT MAX(C1.CCSEQ) FROM ccontact c1 
                       WHERE CC.customer_id = C1.customer_id)

Best wishes

Aggregate functions usually use GROUP BY keyword. – Maciej Los Feb 20 '15 at 20:45 — Maciej Los, Feb 20 '15 at 20:45

score 0 · Answer 1 · answered Feb 20 '15 at 20:44

0

Don't mix plain columns with aggregates if you don't group by those plain columns:

select max(cc.CCSEQ)
from ccontact cc
where customer_id = '724609' and ccbill = 'X';

answered Feb 20 '15 at 20:44

Giorgi Nakeuri

35,155
8
47
75

score 0 · Answer 2 · answered Feb 20 '15 at 20:44

It's difficult to determine the absolute right query without knowing the schema, but my guess is that you want the Max CCSEQ and the respective ccline4, ccstreetno, and cccity for your selection. There are a few ways to do this. With a subquery it would look like:

SELECT
    cc.ccseq, cc.ccline4, cc.ccstreetno, cccity
FROM
    (SELECT max(cc.CCSEQ) AS maxccseq FROM ccontact WHERE customer_id = '724609' and ccbill = 'X') as ccmax
    INNER JOIN ccontact cc ON
        ccmax.maxccseq = cc.CCSEQ

How to get the maxvalue of the following query

2 Answers2