SQL Plus Help

From: Ash <ashok_settyvari_at_yahoo.com>
Date: 18 Mar 2004 07:51:52 -0800
Message-ID: <e7f2e36f.0403180751.cf76d12@posting.google.com>

Hello,

Im trying to use SQL Plus on 2 tables in Oracle database. The code is as follows.

select ALAN8,ALEFTB,ALADD1,ALADD2,ALADD3,ALADD4,ALCTY1,ALADDS,ALADDZ from F0116,F0101 where ABAN8=ALAN8 AND abat1='C' and ALAN8=55

When I execute the above the result is as follows.

   ALAN8 ALEFTB ALADD1

--------- --------- ----------------------------------------
ALADD2                                   ALADD3


----------------------------------------

----------------------------------------

ALADD4                                   ALCTY1                    ALA

ALADDZ
---------------------------------------- ------------------------- ---

---

       55    101298 ATTN:  ANTIONETTE ROMAN
2ND FLOOR                                570 WASHINGTON ST
                                         NEW YORK                  NY 

10080

       55    102077 ATTN:  ANTIONETTE ROMAN
2ND FLOOR                                570 WASHINGTON ST
                                         NEW YORK                  NY 

10014

       55    102157 ATTN:  SHIRLEY LEH
2ND FLOOR                                570 WASHINGTON ST
                                         NEW YORK                  NY 

10014

       55    103022 ATTN:  SHIRLEY LEH
2ND FLOOR                                570 WASHINGTON ST
                                         NEW YORK                  NY 

10014

       55 103031 ATTN: SHIRLEY LEH     ALAN8 ALEFTB ALADD1

--------- --------- ----------------------------------------
ALADD2                                   ALADD3


----------------------------------------

----------------------------------------

ALADD4                                   ALCTY1                    ALA

ALADDZ
---------------------------------------- ------------------------- ---

---

2ND FLOOR                                570 WASHINGTON ST
                                         NEW YORK                  NY 

10014

Now, what I wanted to acheive is, Im getting multiple records from the table F0116 which are matching the criteria. Out of these records, I wanted to get the record where the column ALEFTB is maximum. So I tried using the following code.

select ALAN8,MAX(ALEFTB),ALADD1,ALADD2,ALADD3,ALADD4,ALCTY1,ALADDS,ALADDZ from F0116,F0101 where ABAN8=ALAN8 AND abat1='C' and ALAN8=55;

When I executed the above code, Im getting error not a single-group group function.

Can anyone please help me to acheive this.

Thanks

Ash Received on Thu Mar 18 2004 - 09:51:52 CST