How to remove duplicate/inverted rows from INNER JOIN

Question

I am using SQL to pull from a table, I am also using an INNER JOIN to select to rows with linking values/columns, and appending them into one big row.

Table below:

   | name    |   value    |   num |
   | James   |   HEX124   |   1   |
   | James   |   JEU836   |   4   |

I am now joining these two rows, into one row using this SQL:

SELECT  a.name, a.value, a.num, b.name, b.value, b.num
FROM MY_TABLE a
INNER JOIN MY_TABLE b ON a.name = b.name //Inner joining where the name is the same
WHERE a.value <> b.value // where the values are NO the same

Result:

   | a.name  |   a.value  | a.num | b.name  |   b.value  | b.num |
   | James   |   HEX124   |   1   |  James  |   JEU836   |   4   |
   | James   |   JEU836   |   4   |  James  |   HEX124   |   1   |

As you can see in the result, this is working, but it's returning every possible result, I want to only return one of these rows, as its duplicated/inverted them almost.

Maybe by returning only the first row WHERE a.name is a duplicate?

Desired Result:

   | a.name  |   a.value  | a.num | b.name  |   b.value  | b.num |
   | James   |   HEX124   |   1   |  James  |   JEU836   |   4   |

Thank you

Answer 1

Simply change:

a.value <> b.value

to:

a.value < b.value

I would put the condition in the ON clause, like this:

SELECT a.name, a.value, a.num, b.name, b.value, b.num
FROM MY_TABLE a INNER JOIN
     MY_TABLE b
     ON a.name = b.name AND a.value < b.value;