Hello Group,
I have a table in Oracle that contains records for firemen. Each
record contains information for everytime a fireman is called out.
Each fireman has an ID(unique). What I am trying to do is pull the
first min and second min(or max)for each fireman in terms of the date
they were called out. Each fireman could be called out 100 times per
year. I want to write a query which pulls the first two times each
fireman was pulled out. I have found several different ways of doing
this but it is always for a query written to pull back just one
fireman at a time. I need it for each fireman...
here is a sloppy example of what the data looks like...
Fireman ID Called out date
1 02/02/2010
1 03/03/2009
1 02/13/2009
2 02/02/2010
3 03/03/2005
3 02/13/2006
4 02/02/2006
4 03/03/2007
4 02/13/2010
I would want to pull out the 2 earliest dates for each ID
Here is a query for what i have done so far which doesnt work well and
I think only sort of works for just one fireman at a time:
SELECT *
FROM (SELECT
persons.certified_burner_num,persons.last_name,open_burn_requests.request_date,
open_burn_requests.certified_burn_flag,
open_burn_requests.acres
FROM fmis.open_burn_requests, fmis.persons
WHERE (persons.pk = open_burn_requests.by_pers_fk)
AND open_burn_requests.acres > 0
AND open_burn_requests.request_date > TO_DATE ('01/01/2006
0:01:00', 'MM/DD/YYYY HH24:MI:SS')
AND CERTIFIED_BURNER_NUM in
(19870006,19870013,19870024,19870029,19870030,19870033,19870093,19870118,19870125,19870158,19870166,19870170,19870225,19870250)
order by 3)
WHERE ROWNUM<=2;
Any help would be great appreciated.
Lee
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
