Here is the table:
Activity table
|Place of order|Order NR | Nr of activity|type of activity| Date of activity|
|01 |1 | 00001 | 1 | Date1 |
|01 |1 | 00002 | 1 | Date1 |
|01 |1 | 00003 | 2 | Date2 |
|02 |1 | 00001 | 1 | Date9 |
|01 |2 | 00001 | 1 | Date4 |
|01 |2 | 00003 | 2 | Date5 |
|01 |2 | 00002 | 3 | Date3 |
|02 |2 | 00001 | 1 | Date10 |
|02 |2 | 00006 | 2 | Date11 |
|02 |2 | 00018 | 2 | Date11 |
|02 |1 | 00002 | 2 | Date1 |
Place of order and order number have to be together for uniqueness,
Activity Type 1 is order placed; and
Activity Type 2 is order dispatched
there are other activites that are not interesting in this context.
There was an error (don't know how it happened). For a few orders (Place of order and Order Nr.), the same order has been placed and discharged twice on the same day, but it has a different activity nr.
I am trying to get dates of activity type 1 and type 2 for each order along with the place of order and order Nr.
Results should look like:
For Orders Placed:
|Place of order|Order NR | Nr of activity|type of activity| Date of activity|
|01 |1 | 00001 | 1 | Date1 |
|02 |1 | 00001 | 1 | Date9 |
|01 |2 | 00001 | 1 | Date4 |
|02 |2 | 00001 | 1 | Date10 |
For Orders discharge:
|Place of order|Order NR | Nr of activity|type of activity| Date of activity|
|01 |1 | 00003 | 2 | Date2 |
|02 |1 | 00002 | 2 | Date12 |
|01 |2 | 00002 | 2 | Date5 |
|02 |2 | 00006 | 2 | Date11 |
I can't figure out how to exclude the duplicates from the results. That means I need only one row (the row with minimum activity number).
Please try this :
;with cte as(
select rank() over(order by [Nr of activity] ) as rid , * from TableName
) select * from cte where rid=1