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

 

[carp]

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

This assumes that if you have multiple rows where optm is the same and equal to the minimum optm, you want the lowest casnum.

 

[carp]

Ooops. Forgot the GROUP BY:

Select min(casnum), to_char(optm, 'hh24:mi:ss'), cltm, code, clnum
from cashist
where optm = (SELECT min(optm)
FROM cashist
WHERE opendate between '2001-01-01' and '2001-01-30')
GROUP BY to_char(optm, 'hh24:mi:ss'), cltm, code, clnum;