How to use MAX() in where clause

Question

In my sql statement, i am trying to use MAX() to find the last transaction date but I was getting error.

Here is my query:

select
c.branch, c.cust_no, c.name, c.dobirth, c.cust_sex,c.address, c.phone, c.group_name, c.group_code, c.INCOR_DATE, 
(l.branch+l.gl_no+l.ac_no) as cust, SUM(l.loan_amt) as lamt, l.ac_no, l.cycle, l.exp_date, l.inst_type, l.trx_no,
l.ln_period, l.full_paid, l.Class_Age, l.disb_date, max(h.trx_date) as last_trx,
(h.principal + h.interest) as instalment, (h.principal + h.interest) as overdue,
h.trx_date

from customer c, loans l, loanhist h

where c.cust_no = l.ac_no and l.full_paid != 1 and c.cust_no = h.ac_no  and MONTH(h.trx_date) = MONTH(GETDATE()) 
and YEAR(h.trx_date) = YEAR(GETDATE()) and h.trx_type != 'lp' and h.trx_date = MAX(h.trx_date)

group by c.branch, c.cust_no, c.name, c.dobirth, c.cust_sex,c.address, c.phone, c.group_name, c.group_code, c.INCOR_DATE,
l.ac_no, l.cycle, l.exp_date, l.inst_type, l.trx_no,
l.ln_period, l.full_paid, l.Class_Age, 
l.disb_date, l.branch,l.gl_no, h.principal, h.interest, h.trx_date

Here is the error it was giving me:

Msg 147, Level 15, State 1, Line 11
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

@Tab Alleman, how is that related to my questions? – Fagbemi Ayodele Sep 18 '17 at 15:55 — Fagbemi Ayodele, Sep 18 '17 at 15:55

score 1 · Answer 1 · answered Sep 18 '17 at 15:45

1

Move it to the HAVING clause (like the error states).

Having occurs after the group by.

having h.trx_date = MAX(h.trx_date)

answered Sep 18 '17 at 15:45

Igor

60,821
10
100
175

Getting error: ORA-00979: not a GROUP BY expression – Alisha Raju Jan 31 '23 at 12:29

score 0 · Answer 2 · answered Sep 18 '17 at 15:46

0

You need to use the HAVING clause, which was designed for that:

HAVING h.trx_date = MAX(h.trx_date)

And you place it below the GROUP BY clause.

answered Sep 18 '17 at 15:46

sagi

40,026
6
59
84

Any particular reason for downvoting ? – sagi Sep 18 '17 at 15:50

How to use MAX() in where clause

2 Answers2