problem with retveing data

• chanukae

  Newbie

  Points: 1

  More actions

  July 23, 2011 at 4:25 am

  #244790

  hi,

  i have the following table :Products(item1,item2,total)

  with the data:

  item1 | item2 | total |

  1135 | 1190 | 5 |

  1190 | 1135 | 5 |

  1120 | 1107 | 4 |

  1107 | 1120 | 4 |

  1200 | 1455 | 3 |

  1455 | 1200 | 3 |

  and i want the records : 1135, 1190 , 5

  1120, 1107 , 4

  1200, 1455 , 3

  how do i get them?

  i have many "double" records like this.

  lets say that a "double" value look like this

  1135,1190,5

  1190,1135,5

  thanks

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 23, 2011 at 7:24 am

  #1357417

  Is this what you are looking for?:

  CREATE table #Products(item1 INT,item2 INT,total INT)

  INSERT INTO #Products

  SELECT 1135 , 1190 , 5 UNION ALL

  SELECT 1190 , 1135 , 5 UNION ALL

  SELECT 1120 , 1107 , 4 UNION ALL

  SELECT 1107 , 1120 , 4 UNION ALL

  SELECT 1200 , 1455 , 3 UNION ALL

  SELECT 1455 , 1200 , 3

  ;WITH Cte AS

  (SELECT Row_Number() OVER(PARTITION BY Total ORDER BY Total) AS rn, Item1,item2, total

  FROM #Products)

  SELECT * FROM Cte WHERE rn = 1

  Results:

  rnItem1item2total

  1120014553

  1112011074

  1113511905

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]