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
>>
>
>
--
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
