you can use PL SQL cursor also :)
On 1/7/11, Michael Moore <michaeljmo...@gmail.com> wrote:
> PS,
> this does not cover the case where a fireman has never been called out, but
> you didn't say you needed that. If so, let us know.
> Mike
>
> On Thu, Jan 6, 2011 at 2:03 PM, Michael Moore
> <michaeljmo...@gmail.com>wrote:
>
>> This should tell you what you need to do.
>>
>> SELECT *
>> FROM (SELECT do.customer_id,
>> do.order_timestamp,
>> do.order_total,
>> ROW_NUMBER ()
>> OVER (PARTITION BY customer_id ORDER BY order_timestamp
>> DESC)
>> r_num
>> FROM demo_orders do)
>> WHERE r_num < 2;
>>
>> Let me know if you need further explanation.
>> Regards,
>> Mike
>>
>>
>> On Thu, Jan 6, 2011 at 12:42 PM, crocboy25 <icestor...@hotmail.com> wrote:
>>
>>> 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 Oracle-PLSQL@googlegroups.com
>>> To unsubscribe from this group, send email to
>>> oracle-plsql-unsubscr...@googlegroups.com
>>> For more options, visit this group at
>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>
>>
>>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to Oracle-PLSQL@googlegroups.com
> To unsubscribe from this group, send email to
> oracle-plsql-unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
--
http://www.google.com/profiles/kkumar.sandeep89
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
