Easy View question 1

[Reebo99]

Hi all,

I want to create a view in oracle to only show the last record in a group.

Example data

REF_NUM TRAN_DATE TRAN_NUMBER
10921671 02/01/2003 000009
10921671 10/01/2003 001858
10921671 13/01/2003 000372
0224529015 02/01/2003 009921
0224529015 10/01/2003 000110
0224529015 14/01/2003 000011

The view should only show:
REF_NUM TRAN_DATE TRAN_NUMBER
10921671 13/01/2003 000372
0224529015 14/01/2003 000011

Please help me, it's driving me slowly crazy!!


Reebo
Scotland (Sunny with a Smile)

 

[jee]

Assuming your dates are really date datatypes:

SELECT ref_num, tran_date, tran_number
  from table1 t1, (
       SELECT MAX (tran_date) max_date, ref_num
         FROM table1
        GROUP BY ref_num) t2
 WHERE t1.tran_date = t2.max_date
   AND t1.ref_num = t2.ref_num;

 

[Reebo99]

Thank you.....It all works perfectly!

Reebo
Scotland (Sunny with a Smile)

 

[Reebo99]

Whoops.....spoke too soon.

A new sample of data:

REF_NUM TRAN_DATE TRAN_NUMBER
10921671 02/01/2003 000009
10921671 10/01/2003 001858
10921671 13/01/2003 000372
10921671 13/01/2003 000377
0224529015 02/01/2003 009921
0224529015 10/01/2003 000110
0224529015 14/01/2003 000011
0224529015 14/01/2003 000018

The view should only show:

REF_NUM TRAN_DATE TRAN_NUMBER
10921671 13/01/2003 000377
0224529015 14/01/2003 000018

it currently shows :
REF_NUM TRAN_DATE TRAN_NUMBER
10921671 13/01/2003 000372
10921671 13/01/2003 000377
0224529015 14/01/2003 000011
0224529015 14/01/2003 000018

Sorry for missing out a very important part of the data, you can have upto 1000 different tran_numbers on one day! We are only interested in the last one. The numbers are sequential with the highest number being the last.

Thank you in advance.......

Reebo
Scotland (Sunny with a Smile)

 

[bgill]

Jee's script looks ok.
Did you drop the original view?
Use CREATE OR REPLACE VIEW syntax when creating the view.

 

[Reebo99]

Jee's script is only bringing back the latest TRN_DATE, but because there are multiple TRN_NUMBER for single TRN_DATE then it is showing all TRN_NUMBER for the MAX(TRN_DATE).

Reebo
Scotland (Sunny with a Smile)

 

[bgill]

OK, When the tran_date is recorded is there a time element involved. Can you manipulate to read the max tran_date in the format DD-MON-YYYY HH:MI:SS

example

SELECT to_char(sysdate,'dd Mon YYYY HH24:MI:SS') from DUAL

 

[Reebo99]

No, only the date is stored, not the datetime....

Reebo
Scotland (Sunny with a Smile)

 

[bgill]

What result do you get if you execute the following?

select MAX (to_char (TRAN_DATE, 'dd Mon YYYY HH24:MI:SS'))from YOUR_TABLE_NAME

 

[Reebo99]

17 Jan 2003 00:00:00

Reebo
Scotland (Sunny with a Smile)

 

[Reebo99]

Sorry, I was in the wrong database......

31 Dec 2002 00:00:00

Reebo
Scotland (Sunny with a Smile)

 

[Reebo99]

I think i've got it!!

SELECT ref_num, tran_date, MAX(tran_number)
from table1 t1, (
SELECT MAX (tran_date) max_date, ref_num
FROM table1
GROUP BY ref_num) t2
WHERE t1.tran_date = t2.max_date
AND t1.ref_num = t2.ref_num;
GROUP BY ref_num, tran_date

Any comments?




Reebo
Scotland (Sunny with a Smile)

 

[bgill]

Ok try jee's original script on the same sample data in your third post.

ie.

SELECT ref_num, tran_date, tran_number
from table1 t1, (
SELECT MAX (to_char (TRAN_DATE, 'dd Mon YYYY HH24:MI:SS')) max_date, ref_num
FROM table1
GROUP BY ref_num) t2
WHERE t1.tran_date = t2.max_date
AND t1.ref_num = t2.ref_num;

what result do you get now?
Is the data type from column tran_date 'DATE' datatype?
If you cant extract the max date (using the time the transaction occurred) you need to look at the design of the table. Are there any other columns, perhaps a last_update_time for each row that you could use

 

[Reebo99]

Thank you all for your help......it all works very well now..

Reebo
Scotland (Sunny with a Smile)

 

[bgill]

Yes sorry! should have seen from your sample data
Each transaction has a number. Although will only work if the max tran_number corresponds to the latest transaction.

 

[bgill]

Hold on
In your third post you said

The view should only show:

REF_NUM TRAN_DATE TRAN_NUMBER
10921671 13/01/2003 000377
0224529015 14/01/2003 000018


Yet your sample data shows

A new sample of data:

REF_NUM TRAN_DATE TRAN_NUMBER
10921671 02/01/2003 000009
10921671 10/01/2003 001858
10921671 13/01/2003 000372
10921671 13/01/2003 000377
0224529015 02/01/2003 009921
0224529015 10/01/2003 000110
0224529015 14/01/2003 000011
0224529015 14/01/2003 000018

Should the result not show

REF_NUM TRAN_DATE TRAN_NUMBER
10921671 10/01/2003 001858
0224529015 02/01/2003 009921


this is what it should show if you are selecting on max tran_number. This is different to what yoy said it should show. Like I said you need to be sure that the max tran_number corresponds to the latest transaction.

I have to leave now. do some more checks to be sure.

Good luck