CODEWITHSUNDEEP
sqlhana-studio
sveer
sveer

Reputation: 472

need one record in duplicate records

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).

Upvotes: 3

Views: 229

Answers (1)

Antony
Antony

Reputation: 976

Please try this :

  ;with cte as(
    select rank() over(order by [Nr of activity] ) as rid , * from TableName
    ) select * from cte where rid=1

Upvotes: 1

Related Questions