How to exclude duplicate rows when joining a table with itself
group-by, oracle, outer-join, sql
Solution
Just remove a single character from your query and it will get the job done... (replace "<>" with "<"):
select * from test T1
LEFT OUTER JOIN test T2 on T1.siteid = T2.siteid and T1.id < T2.id
Problem
Here's a sample table to help illustrate my problem: ``` mysql> select * from test; +----+--------------+--------+ | id | type | siteid | +----+--------------+--------+ | 1 | First Visit | 100 | | 2 | Second Visit | 100 | | 3 | First Visit | 300 | | 4 | First Visit | 400 | | 5 | Second Visit | 500 | | 6 | Second Visit | 600 | +----+--------------+--------+ ``` I'm trying to join the table upon itself, to pull together rows that have the same siteid value. Here's my attempt: ``` mysql> select * from test T1 -> LEFT OUTER JOIN test T2 on T1.siteid = T2.siteid and T1.id <> T2.id; +----+--------------+--------+------+--------------+--------+ | id | type | siteid | id | type | siteid | +----+--------------+--------+------+--------------+--------+ | 1 | First Visit | 100 | 2 | Second Visit | 100 | | 2 | Second Visit | 100 | 1 | First Visit | 100 | | 3 | First Visit | 300 | NULL | NULL | NULL | | 4 | First Visit | 400 | NULL | NULL | NULL | | 5 | Second Visit | 500 | NULL | NULL | NULL | | 6 | Second Visit | 600 | NULL | NULL | NULL | +----+--------------+--------+------+--------------+--------+ ``` This is basically the result I'm looking for, except for the 1st 2 rows. I'd like to eliminate one of those. So, I tried the following: ``` mysql> select * from test T1 -> LEFT OUTER JOIN test T2 on T1.siteid = T2.siteid and T1.id <> T2.id -> GROUP BY T1.siteid; +----+--------------+--------+------+--------------+--------+ | id | type | siteid | id | type | siteid | +----+--------------+--------+------+--------------+--------+ | 1 | First Visit | 100 | 2 | Second Visit | 100 | | 3 | First Visit | 300 | NULL | NULL | NULL | | 4 | First Visit | 400 | NULL | NULL | NULL | | 5 | Second Visit | 500 | NULL | NULL | NULL | | 6 | Second Visit | 600 | NULL | NULL | NULL | +----+--------------+--------+------+--------------+--------+ ``` This is exactly the output I'm looking for. However, I come to learn this isn't the standard way to use GROUP BY, and the above statement fails on ORACLE, giving me a General SQL error. ORA-00979: not a GROUP BY expression Can anyone offer some help on how to get the results like the last table and that works with ORACLE?