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 <[email protected]> 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 [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
