SQL distinct/groupby on combination of columns

I am trying to do a SQL select on a table based on two columns, but not in the usual way where the combination of values in both columns must be unique; I want to select where the value can only appear once in either column. Given the dataset:

|pkid | fkself | otherData |
|-----+--------开发者_运维问答+-----------|
|  1  |   4    |   there   |
|  4  |   1    |    will   |
|  3  |   6    |     be    |
|  2  |   5    |    other  |
|  5  |   2    |   data    |
|  6  |   3    |  columns  |

I need to return either

|pkid | fkself | otherData |
|-----+--------+-----------|
|  1  |   4    |   there   |
|  3  |   6    |     be    |
|  2  |   5    |    other  |

or

|pkid | fkself | otherData |
|-----+--------+-----------|
|  4  |   1    |    will   |
|  5  |   2    |   data    |
|  6  |   3    |  columns  |

The only way I can think of to do this is to concatenate `pkid and fkid in order so that both row 1 and row 2 would concatenate to 1,4, but I'm not sure how to do that, or if it is even possible.

The rows will have other data columns, but it does not matter which row I get, only that I get each ID only once, whether the value is in pkid or fkself.

You can use least and greatest to get the smallest or biggest value of the two. That allows you to put them in the right order to generate those keys for you. You could concatenate the values as you suggested, but it's not needed in this solution. With dense_rank you can generate a sequence for each of those fictional keys. Then, you can get the first OtherData from that sequence.

select
  pkid,
  fkself,
  otherData
from
  (select
    pkid,
    fkself,
    otherData,
    dense_rank() over (partition by least(pkid, fkself), greatest(pkid, fkself) order by pkid) as rank
  from
    YourTable t)
where
  rank = 1

Your idea is possible, and it should produce the results you want.

SELECT DISTINCT joinedID
FROM (
SELECT min(id) & "," & max(id) as joinedID
FROM (
    SELECT pkid as id, someUniqueValue 
    FROM table 
    UNION ALL 
    SELECT fkself as id, someUniqueValue 
    FROM table)
GROUP BY someUniqueValue )

This will give you a unique list of IDs, concatenated as you like. You can easily include other fields by adding them to each SELECT statement. Also, someUniqueValue can be either an existing unique field, a new unique field, or the concatenated pkid and fkself, if that combination is unique.

The only way I can think of to do this is to concatenate `pkid and fkid in order so that both row 1 and row 2 would concatenate to 1,4, but I'm not sure how to do that, or if it is even possible.

You could do it using a CASE statement in Oracle:

SQL> SELECT * FROM sample
  2  /

      PKID     FKSELF
---------- ----------
         1          4
         4          1
         3          6
         2          5
         5          2
         7          7

6 rows selected.

SQL> l
  1  SELECT DISTINCT *
  2  FROM (
  3  SELECT CASE WHEN pkid <= fkself THEN pkid||','||fkself
  4                                  ELSE fkself||','||pkid
  5         END "JOINED"
  6    FROM sample
  7* )
SQL> /

JOINED
-------------------------------------------------------------------------------
1,4
2,5
3,6
7,7