Getting first occurance

[wsam]

I am trying to get the first occurance of the code field for each CAS# (Results only have one cas#)

How would I do that using my select statement:

Select casnum, to_char(optm, 'hh24:mi:ss'), cltm, code, clnum
from cashist
where opendate between '2001-01-01' and '2001-01-30'

RESULTS:

CAS# OPEN TM CL TM CODE CL#
____ _______ ______ ____ ___

20010106 08:05:10 08:14:29 DRP 6
20010106 08:06:08 08:14:29 DRP 9
20010106 08:06:18 08:14:29 ESP 11
20010106 08:06:43 08:14:29 ESP 14
20010106 08:06:48 08:14:29 ESP 15
20010106 08:10:47 08:14:29 ODP 20
20010106 08:10:57 08:14:29 ODP 21

Thanks in advance

 

[ThomVF]

What determines the "First occurence" of a code? Is it open-time?

If first occurence is determined by open time:

select casnum, to_char(optm, 'hh24:mi:ss'), cltm, code, clnum
from cashist where (casnum, optm) in (
select casnum, min(optm) from cashist group by casnum);

Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[lewisp]

How about

Select casnum, to_char(optm, 'hh24:mi:ss'), cltm, code, clnum
from ( Select casnum, to_char(optm, 'hh24:mi:ss'), cltm, code, clnum
from cashist
where opendate between '2001-01-01' and '2001-01-30')
order by optm desc)
where rownum = 1;

 

[ThomVF]

I think lewisp's example will work if there is only one cas#, as your example shows.

Will there be more than one in reality?
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[wsam]

Yes, there are many cas# with the same type of data.