Results 1 to 3 of 3

Thread: sql query ??

  1. #1
    Join Date
    Jun 2005
    Location
    Chennai
    Posts
    16

    Unanswered: sql query ??

    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
    Learning, Keep on learning.....

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •