3

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

sveer
  • 427
  • 3
  • 16
  • MySQL or sql-server? – Raymond Nijland Feb 24 '17 at 10:21
  • I don't know the difference, I need SQL statements :) – sveer Feb 24 '17 at 10:32
  • Once you know if you're working with MySQL or SQL Server, then tag the correct product – James Z Feb 24 '17 at 10:36
  • 1
    Run `select @@version` if the result is just a number it's MySQL otherwise it will show you the version of "Microsoft SQL Server" you are using. –  Feb 24 '17 at 10:40
  • SQL scripts are written SAP HANA studio! – sveer Feb 24 '17 at 10:45
  • I guess "Place of order and order number have to be together for uniqueness" means those colums identify an order? I guess you mean that an order should be placed only once and discharged only once (on at least that date)? I guess the an order's correct placing & discharging are the ones with the smallest activity number? (You are not clear.) (You show the table & say that it shouldn't have a certain property but you don't make clear just what the table should look like.) Please don't write in fragments. – philipxy Feb 24 '17 at 11:04
  • Dear Philipxy, All your guesses are correct, I gave the Table, explained the table. and I gave the tables how i want them to be and I mentioned where i have the problem. I will consider your suggestion and make my next posts very clear. thanks! – sveer Feb 24 '17 at 12:00

1 Answers1

1

Please try this :

  ;with cte as(
    select rank() over(order by [Nr of activity] ) as rid , * from TableName
    ) select * from cte where rid=1
Antony
  • 966
  • 8
  • 19
  • @Anotny wait, its giving something, let me check! – sveer Feb 24 '17 at 12:08
  • hey, it did seems working **without `;`**, but a small problem. The records with activity type 2 which were double are `n` in number. My investigation shows there are only twice marked as dischrged. That being said the table should have `2*n` of double records before filtering and `n` records after filtering. I get `n-150` records. Any suggestions or comments? – sveer Feb 24 '17 at 12:44
  • Another issue is I cannot join or combine this statement to another statement with Where IN for example! – sveer Feb 24 '17 at 15:24
  • O o.. the RID changes very drastically if the Activity Nr changes! it takes Number of the row when the Nr of Activity changes as rid. – sveer Feb 24 '17 at 15:39
  • @sai We can join to the multi statements in CTE – Antony Feb 27 '17 at 12:52
  • @RID changes to a certain number where the type changes. I didnt get the logic though! – sveer Feb 27 '17 at 21:03
  • 1
    Yes correct , If RID=1 its distinct data and Rid >1 means its duplicate – Antony Feb 28 '17 at 10:44