How can I do select in sql server using two conditions ?

See more:

SQL

SQL-Server

SQL-Server-2008R2

, +

I have a table "ProductStatusHistory" witch contain the history of a Product request.

ID   ID_Product   ID_Request   Status            Category   ID_Lab   Created_ON
1     40           5         Clinic Requested   Cat1       2       02-09-2015 08:00:00
2     40           5         Lab Request        Cat1       2       03-09-2015 08:00:00
3     40           5         Started            Cat1       2       04-09-2015 08:00:00
4     40           5         Completed          Cat1       2       05-09-2015 08:00:00
5     41           5         Clinic Request     Cat1       2       02-09-2015 08:00:00
6     41           5         Lab Request        Cat1       2       03-09-2015 09:00:00
7     41           5         Started            Cat1       2       04-09-2015 09:00:00
8     41           5         Cancelled          Cat1       2       05-09-2015 09:00:00

I need to select the product for each request (a Request (ID_Request) can have 1 or more product request (ID_Product)) with the last status associated. The output that I need it should be like this:

ID_Request    ID_Product    Status       Category     ID_Lab      Created_ON   
5               40          Completed      Cat1         2       05-09-2015 08:00:00
5               41          Cancelled      Cat1         2       05-09-2015 09:00:00

I have tried this query:

select   IDProduct 
      ,  SelectedProd
      ,  Category
      ,  CreatedOn
      ,  Status
from ProductStatusHistory
where ID_Req=5 and CreatedOn=(select  max(CreatedOn) from ProductStatusHistory) 

But it gives me always the last row in the table for the indicated request ID.

ANY SOLUTION PLEASE ?