can anyone write a sql query ? without using view or CTE.

what are the possible methods to derive the result ?
Here is the table
---------+---------+---------+---------+---------+---------+-
NAME ACCT_NR TOT_AMT
---------+---------+---------+---------+---------+---------+-
RAMAN 517000000 500.000000000
RAMAN 517000001 63.000000000
RAMAN 517000002 110.000000000
RAMAN 517000003 170.000000000
SURESH 517000004 210.000000000
SURESH 517000000 46.000000000
SURESH 517000001 83.000000000
DINESH 517000002 615.000000000
DINESH 517000003 190.000000000

I want the name of the person,account num,amt having the maximum amt.

Result should be :
RAMAN 517000000 500.000000000
SURESH 517000004 210.000000000
DINESH 517000002 615.000000000

select E.* from tab E
join
(select name,max(TOT_AMT ) as max from tab group by name) as A
on E. name=A.name
and E.TOT_AMT =A.max

that should work
-- Rahul Singh

This is probably more intuitive:

SELECT A.NAME, A.ACCT_NR, A.TOT_AMT FROM T1 A
WHERE A.TOT_AMT =
(SELECT MAX(B.TOT_AMT) FROM T1 B
WHERE B.ACCT_NR = A.ACCT_NR)