Avoiding multiple reference to same value in Query

[ashsa]

Hi all..

I am having a table FTP_FILE_SEQUEN CE_STATUS with fields

FILE_TYPE  ----- varchar2(10)
FILE_SEQUENCE_NO ------- Number(7)
GENERATION_DATE  ------ Date
STATUS  -------- varchar2(1)

There are three filetypes a,b and c.
Now i want to get the status of the file type 'a' which is having the highest file sequence number. I am using the following query and its working :-)

select status from FTP_FILE_SEQUENCE_STATUS where FILE_TYPE='a' and FILE_SEQUENCE_NO = (select max(FILE_SEQUENCE_NO) from FTP_FILE_SEQUENCE_STATUS where file_type='a')

But i have been asked to remove the double check for file_type. :-(
Any other way to get the status without checking for file_type twice ??

Any help would be highly useful ..

Many Thanks in advance..

[debasisdas]

If u are checking in the sub-query ,why u need to check that in hte outer query again.

[ashsa]

If u are checking in the sub-query ,why u need to check that in hte outer query again.

There are Three types of files a,b and c and i want to get the status of the latest sequence of type 'a'.

file type 'a' might have the sequence 200 and file type 'b' might have the sequence 201. But i want only the status of file type 'a' that has been generated last meaning that status of the latest generated file in a particular type.

[ashsa]

There are Three types of files a,b and c and i want to get the status of the latest sequence of type 'a'.

file type 'a' might have the sequence 200 and file type 'b' might have the sequence 201. But i want only the status of file type 'a' that has been generated last meaning that status of the latest generated file in a particular type.

Got your point !!
Thanks a lot ...