Selecting and printing out duplicates in SQL? (No duplicate)

Question

The question looks like a duplicate because I have found a lot of answers related to this but even thought I tried everything, nothing answered my problem.

I want to select all the rows which have the same value(category_max). I have tried this query:

SELECT w1.user_name, w1.wallet_adr, w1.category_min, w1.category_max,
w2.user_name, w2.wallet_adr, w2.category_min, w2.category_max
FROM WaitingPlayers w1, WaitingPlayers w2
WHERE w1.category_max = w2.category_max
AND w1.wallet_adr <> w2.wallet_adr

I have also tried it with inner join as you can see here:

SELECT 
w1.user_name, w1.wallet_adr, w1.category_min, w1.category_max,
w2.user_name, w2.wallet_adr, w2.category_min, w2.category_max
FROM WaitingPlayers w1
INNER JOIN WaitingPlayers w2 ON w2.category_max = w1.category_max 
WHERE w2.wallet_adr != w1.wallet_adr

But the result is always the same:

http://i61.tinypic.com/10nyus3.png

This is what I want:

http://i60.tinypic.com/2lvxjk.png

Wallet_adr and username are unique so they really should not be displayed twice unless that selection w1 and w2 is considered as select twice.

Any help appreciated.

Answer 1

you need a group by:

 WHERE w2.wallet_adr != w1.wallet_adr 
 GROUP BY w1.user_name

EDIT: instead of

  SELECT w1.user_name, w1.wallet_adr, w1.category_min, w1.category_max,
  w2.user_name, w2.wallet_adr, w2.category_min, w2.category_max
  FROM ...

use

 SELECT w1.user_name, w1.wallet_adr, w1.category_min, w1.category_max
 FROM ....

Answer 2

This has all pairs, which could get quite large:

SELECT w1.user_name, w1.wallet_adr, w1.category_min, w1.category_max,
       w2.user_name, w2.wallet_adr, w2.category_min, w2.category_max
FROM WaitingPlayers w1 join
     WaitingPlayers w2
     on w1.category_max = w2.category_max AND
     w1.wallet_adr <> w2.wallet_adr;

I imagine the following would be more useful:

select wp.*
from WaitingPlayers wp join
     (select wp.category_max, count(*) as cnt
      from WaitingPlayers wp
      group by wp.category_max
      having cnt > 1
     ) wpcnt
     on wp.category_max = wpcnt.category_max
order by cnt desc, wp.category_max;

If you need for the duplicates to have more than one wallet_adr, change the subquery to:

select wp.*
from WaitingPlayers wp join
     (select wp.category_max, count(distinct wp.wallet_adr) as cnt
      from WaitingPlayers wp
      group by wp.category_max
      having cnt > 1
     ) wpcnt
     on wp.category_max = wpcnt.category_max
order by cnt desc, wp.category_max;

This will put duplicates on a separate line.