0

Is there a way to cause a UNION to remove duplicate results only based off of certain columns? From what I understand about the UNION is that it will only remove a duplicate row based off of all the fields in the duplicate results being identical. For example: is it possible to alter the UNION in some way as to say "remove duplicate results, only when columns 1,4 & 5 are identical?"

user3630205
  • 1
  • Which values in columns other than 1,4, and 5 would you choose? First? Highest? Average? – D Stanley May 12 '14 at 22:01
  • http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem/66378#66378 – Mihai May 12 '14 at 22:04
  • no, there isn't. What you can do is create a unique index on those columns and make it so it ignores duplicates – Lamak May 12 '14 at 22:12

1 Answers1

0

There is only a UNION and a UNION ALL

You could use union to retrieve unique records and add a calculated field to achieve your need for uniqueness.

SELECT
    MyID,
    MyCalcValue=CASE WHEN MyID IN(1,3,4) THEN NEWID() ELSE NULL
FROM
  MyTable

UNION

SELECT
    MyID,
    MyCalcValue=CASE WHEN MyID IN(1,3,4) THEN NEWID() ELSE NULL
FROM
  MyTable2
Ross Bush
  • 14,648
  • 2
  • 32
  • 55