Glad I could help.
On Fri, Jan 7, 2011 at 12:25 PM, crocboy25 <icestor...@hotmail.com> wrote:
> Mike,
>
> The user LOVED it. Amazing functionality in a very small amount of
> code and also has great performance. It saved the user(and myself) a
> lot of time.
>
> Thank you
>
> Lee
>
> On Jan 7, 2:14 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
> > One more thing. If you really want to improve the performance, do the
> join
> > to fmis.persons in the outer select, not the inner select. That way, you
> > will only be doing the join 2 times per burner.
> >
> > Mike
> >
> > On Fri, Jan 7, 2011 at 11:09 AM, Michael Moore <michaeljmo...@gmail.com
> >wrote:
> >
> >
> >
> > > PS.
> >
> > > Your unintentional cross-join is an excellent example of why it is
> better
> > > to use SQL92 Join syntax.
> >
> > > instead of
> > > FROM fmis.open_burn_requests, fmis.persons
> > > do
> > > FROM fmis.open_burn_requests join fmis.persons on persons.fkey =
> > > open_burn_requests.key
> >
> > > where fkey and key are the columns to be joined.
> >
> > > Also, always qualify alias your table names and qualify your column
> names
> > > with that alias.
> > > Never use the table name to qualify your column names as you have done.
> >
> > > So instead of
> > > SELECT persons.certified_burner_num from fmis.persons;
> > > do
> > > select p.certified_burner_num from fmis.persons p;
> >
> > > There are good reasons for doing this that are not apparent in such a
> > > simple example. I don't really want to take the time to explain why
> this is
> > > good, so you have the option to take my word for it or not.
> >
> > > Regards,
> > > Mike
> >
> > > On Fri, Jan 7, 2011 at 10:21 AM, crocboy25 <icestor...@hotmail.com>
> wrote:
> >
> > >> I can't use PL SQL for this task. It is for a user to use from time to
> > >> time and the person is not tech savy. I am trying to dumb it down.
> >
> > >> On Jan 7, 4:56 am, sandeep kumar <kkumar.sandee...@gmail.com> wrote:
> > >> > 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-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- 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
