Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    IL
    Posts
    5

    Arrow Unanswered: Puzzled by duplicates

    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!

  2. #2
    Join Date
    Oct 2003
    Posts
    16

    Re: Puzzled by duplicates

    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/

  3. #3
    Join Date
    Sep 2003
    Location
    IL
    Posts
    5

    Arrow Re: Puzzled by duplicates

    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.

  4. #4
    Join Date
    Oct 2003
    Posts
    16

    Cool Re: Puzzled by duplicates

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •