SQL Server - how to create my select statement

Asked By Steph

16-Nov-09 01:58 PM

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

16-Nov-09 03:38 PM

If I understand what you want, then

16-Nov-09 03:39 PM

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

17-Nov-09 02:14 PM

that is perfect!
it works
thanks