Having problems running query in Oracle

From: George <gmavrot_at_yahoo.com.au>
Date: 26 Oct 2003 17:22:40 -0800
Message-ID: <9ff50e93.0310261722.3bc28e78_at_posting.google.com>

I have two tables and want to find the Maximum date for a given GIN. I have been able to produce the result in Sybase but I am having problems in Oracle.

Example of my query in Sybase.

create table #temp1 (

       groupcode  char(10),
       loc_acc_no int,
       gin        int,
       amount     money,
       brancode   char(10) )

create table #temp2 (
       loc_acc_no int,
       gin        int,
       N_datetime smalldatetime,
       notetext   char(10) )

insert into #temp1 values("GM",26,3920,100.10,'a')
insert into #temp2 values(26,3920,'14 Oct 2003 03:43','Simple')

insert into #temp2 values(26,3920,'14 Oct 2003 03:42','Repair') insert into #temp2 values(26,3920,'08 Oct 2003 06:37','Simple')

insert into #temp1 values("GM",26,3921,200.10,'b')
insert into #temp2 values(26,3921,'08 Oct 2003 06:36','Repair')
insert into #temp2 values(26,3921,'08 Oct 2003 06:34','Repair')
insert into #temp2 values(26,3921,'08 Oct 2003 00:17','Simple')

insert into #temp1 values("GM",27,3922,300.10,'c')

insert into #temp2 values(27,3922,'03 Oct 2003 07:05','Simple') insert into #temp2 values(27,3922,'03 Oct 2003 07:04','Repair')

/******************************************/

My query to produce my result.

select t2.N_datetime,
       t1.groupcode,
       t1.loc_acc_no,
       t1.gin,
       t1.amount,
       t1.brancode,
       t2.notetext
from   #temp1 t1,
       #temp2 t2
where  t1.loc_acc_no = t2.loc_acc_no
and    t1.gin        = t2.gin
group by 
       t1.gin

having t2.N_datetime = max(t2.N_datetime)

Results

---

N_datetime	groupcode	loc_acc_no	gin	amount	brancode	notetext
14/10/2003 03:43:00.000	GM        	26	3920	100.10	a         	Simple
8/10/2003 06:36:00.000	GM        	26	3921	200.10	b         	Repair    
3/10/2003 07:05:00.000	GM        	27	3922	300.10	c         	Simple    

If anyone can help re-write the above query so it works in Oracle that would
be much appreciated.

Thanks
George Received on Mon Oct 27 2003 - 02:22:40 CET