Hello all!

Have a question:

Is there way in SQL to determine duplicate rows without using count(), aggregate functions, group by or select distinct?

I only have regular select, join and delete features.

Basically, what are all the possible ways to determine duplicates in data like this?

col1|col2
----------
s1----j1
s1----j4
s1----j1
s1----j3
s1----j2

I greatly appreciate your response!
Thanks!

You need to add a third column, a unique ID column. Then you can do this

select t1.* from t1
join t2 on t1.col1=t2.col2 and t1.col2=t2.col2 and t1.id != t2.id

Check out my brand new SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html

-Chris
http://www.bitesizeinc.net/

Is there way to do it without a unique ID? Not to be persistent, but my original goal was to do it only with those simple SQL statements.

I just wanted to make sure that I tried every way possible. Maybe there is a way to use cartesian product or whichever, but it's gotta be based on these simple statements.

If it can only be done using unique ID or aggregate functions, it's a good answer as well.

Let me know if I sound confusing. Great site btw, very original!.

Thanks for your devotion.

Thank you so much for visitting my site...

I think that you're out of options here.

The best way is using GROUP BY.

Otherwise, you could select DISTINCT CONCAT(field1,' ',field2)

Or use the unique ID

I can't think of anyway else....

-Chris