Equivalent of MS Access Sql (first or last function) in oracle9i

[Halimat]

I have a table in MS Access and ran a query to return some distinct record, it works fine but a similar table in Oracle 9i using similar query (sql) failed. Find below the MS Access Orignal table, Access Query (sql), and table after rurnning query and the error returned while trying to run same query on Oracle 9i:

Access Orignal table
Name Add Category Bala
ATM 12, OREMEJI STREET A1 LL
ATM1 3, BANA ROAD A3 QQ
ATM 5, BADORE A111 AC
ATM 6, SHOLA STREET A23 AA
ATM1 67, BANA AVENUE A9 BB
ATM2 45, KADOZO A12 WE
ATM3 13, HALIMAT A87 GG
ATM3 23, KAMARA A8 XX
ATM1 78, BOUNCERS A5 TT
ATM2 35, KUDIRAT ABIOLA WAY A2 WE

Access Query (sql)

SELECT DISTINCT ATM.Name, FIRST(ATM.BALA) AS BALA, FIRST(ATM.ADD) AS ADDRESS
FROM ATM
GROUP BY NAME;


table after rurnning query
Name BALA ADDRESS
ATM LL 12, OREMEJI STREET
ATM1 QQ 3, BANA ROAD
ATM2 WE 45, KADOZO
ATM3 GG 13, HALIMAT

error oracle 9i
ORA-00904: "LAST": invalid identifier

[prty]

Please use this query :

 
SELECT DISTINCT ATM.Name, ATM.BALA AS BALA, ATM.ADD AS ADDRESS
FROM ATM
GROUP BY NAME,BALA,ADDRESS;
 
 

[amitpatel66]

Are you sure you ran the same query as above in your oracle 9i??

[QVeen72]

Hi,

Not very sure "First" and "Last" can be used directly without Dense_Rank.

Anyway, if you are Interested with any one of the Names from the List, you can use "Min" or "Max" functions..

SELECT DISTINCT ATM.Name, Min(ATM.BALA) AS BALA, Min(ATM.ADD) AS ADDRESS
FROM ATM
GROUP BY ATM.NAME
 

Regards
Veena