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