>From my initial test it seems to be working.... i am going to send it
to my user and have him test it.
Thanks again Mike for taking the time to help. I appreciate it. I
will post back in a little bit to finalize it...
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
