how to create my select statement

[Steph]

I have a table (tbl_work) which contains these fields

Work_id (Primary Key)
Opportunity_id (Foreign Key)
Work_description
Work_type_code
Work_dt
State_id

I want to create a view with this result:
Work_id, Opportunity_id, Min_State_id, Last_Work_dt

One row /opportunity
I always want to see the work which has the smaller State_id and the highest
date for an opportunity

ex:
work_id, Opportunity_id, Work_type_code, Work_dt,
state_id
1, 1, 'Z',
2009-11-14, 3
2, 2, 'X',
2009-11-13, 1
3, 1, 'L',
2009-11-12, 2
4, 1, 'K',
2009-11-11, 2

Here's the result I want to see
Work_id, Opportunity_id, Min_State_id, Last_Work_dt
2, 2, 'X',
2009-11-13, 1
3, 1, 'L',
2009-11-12, 2

how can I do that?

thanks

Steph

[Tom Cooper]

If I understand what you want, then

"Steph" <smar...@cbgi.qc.ca> wrote in message
news:O$BRT7uZK...@TK2MSFTNGP06.phx.gbl...

[Tom Cooper]

Sorry, hit send by mistake.
If I understand what you want, then (replace <YourTable> with your table
name)

With cte As
(Select work_id, Opportunity_id, Work_type_code, Work_dt, state_id,
Row_Number() Over (Partition By Opportunity_id Order By state_id,
Work_dt Desc) As rn
From <YourTable>)
Select work_id, Opportunity_id, Work_type_code, Work_dt, state_id
From cte
Where rn = 1;

Tom

"Steph" <smar...@cbgi.qc.ca> wrote in message
news:O$BRT7uZK...@TK2MSFTNGP06.phx.gbl...

[Steph]

that's perfect!
it works
thanks

"Tom Cooper" <tomc...@comcast.net> wrote in message
news:eNKTlzvZ...@TK2MSFTNGP02.phx.gbl...