You are doing a cross join on fmis.open_burn_requests, fmis.persons. You
need to fix that. :-)
Mike
On Fri, Jan 7, 2011 at 10:20 AM, crocboy25 <icestor...@hotmail.com> wrote:
> I tried this version of it and it ran for over 2 hours before I
> stopped it...
>
> 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,
> ROW_NUMBER ()
> OVER (PARTITION BY certified_burner_num ORDER BY
> request_date DESC) r_num
> FROM fmis.open_burn_requests, fmis.persons)
> WHERE r_num < 2;
>
>
>
> On Jan 6, 5:06 pm, 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- Hide quoted text -
> >
> > - Show quoted text -
>
> --
> 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
