Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2011
    Posts
    7

    Exclamation Unanswered: identifying duplicate by comparison

    Hi Pls help me in solving this problem in SQL server 2005.
    I have a table in which columns like PID,Partner_sku,title,url,etc..Now i want to find if same partner_sku exist for same pid.i will tell u clearly.
    pid partner_sku
    123 as123
    123 as124
    123 as123
    124 as123
    124 as124

    i want result as below
    PID Partner_sku
    123 as123
    123 as124
    124 as123
    124 as124

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by dinesh12 View Post
    Hi Pls help me in solving this problem in SQL server 2005.
    I have a table in which columns like PID,Partner_sku,title,url,etc..Now i want to find if same partner_sku exist for same pid.i will tell u clearly.
    pid partner_sku
    123 as123
    123 as124
    123 as123
    124 as123
    124 as124

    i want result as below
    PID Partner_sku
    123 as123
    123 as124
    124 as123
    124 as124
    Try using 'having' in place of where clause in select which is ideal for finding dupes.

  3. #3
    Join Date
    Jan 2011
    Posts
    7

    Lightbulb give the query

    Hi cron

    please explain me with exact query

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by dinesh12 View Post
    Hi cron

    please explain me with exact query
    Go to BOL and search using having in the where clause. No sense in giving you the anwer because you'll never learn to fly on your own.

  5. #5
    Join Date
    Jan 2011
    Posts
    7

    Question I know abt having

    I Know the usage of "having" .. i"m asking how to use it hear.

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Select count(*),column_name
    from table
    group by column_name
    having count(*) > 1

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT DISTINCT pid, partner_sku
    FROM MyTable
    Will give you the result like you presented it in
    i will tell u clearly.
    pid partner_sku
    123 as123
    123 as124
    123 as123
    124 as123
    124 as124

    i want result as below
    PID Partner_sku
    123 as123
    123 as124
    124 as123
    124 as124
    But it differs from what you said in wording:
    i want to find if same partner_sku exist for same pid.
    In that case the result would only contain 123 as123. If this is your REAL question, you will indeed have to use HAVING in your SQL statement, together with a GROUP BY. You seem to already know how to use it
    I Know the usage of "having"
    When you are in doubt, give us your best shot, and we will help you from there.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I re-read your question and it is more clear now after several cups of coffee. The dupe check isn't what you want. I think it is writing code to strip off the first two characters in partner_sku.

    Something like:

    Select pid, partner_sku
    from table
    where substring(partner_sku,3,len(partner_sku) = pid
    group by pid,partner_sku

    --- test
    create table #table(pid int,partner_sku varchar(10))
    insert into #table values(123,'as123')
    insert into #table values(123,'as123')
    insert into #table values(124,'as123')
    insert into #table values(124,'as124')
    insert into #table values(125,'as124')
    insert into #table values(125,'as125')
    insert into #table values(126,'as126')
    insert into #table values(126,'as126')
    insert into #table values(126,'as123')
    insert into #table values(127,'as127')

    select pid,partner_sku
    from #table
    where substring(partner_sku,3,len(partner_sku)) = pid
    group by pid,partner_sku

    drop table #table

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Wim View Post
    Code:
    SELECT DISTINCT pid, partner_sku
    FROM MyTable
    Will give you the result like you presented it in But it differs from what you said in wording:In that case the result would only contain 123 as123. If this is your REAL question, you will indeed have to use HAVING in your SQL statement, together with a GROUP BY. You seem to already know how to use itWhen you are in doubt, give us your best shot, and we will help you from there.
    I believe select distinct would only work on two columns presented in results, but rest of record could be different...it's the wording and reading question several times....

  10. #10
    Join Date
    Jan 2011
    Posts
    7

    Thumbs down totally out of track

    hey u was totally out of track.
    I think wim got the point. I want to mark a record as duplicate if same partner_sku exist twice for single PID. I want to display only distinct records.
    PID Partner_sku
    10001 asdf
    10001 asdf
    10002 asdf
    10002 ajhdf

    result should be:
    PID Partner_sku
    10001 asdf
    10002 asdf
    10002 ajhdf

  11. #11
    Join Date
    Jan 2011
    Posts
    7

    perfect

    i want to do one more thing in this. I want mark duplicate records as "duplicate"

    Quote Originally Posted by Wim View Post
    Code:
    SELECT DISTINCT pid, partner_sku
    FROM MyTable
    Will give you the result like you presented it in But it differs from what you said in wording:In that case the result would only contain 123 as123. If this is your REAL question, you will indeed have to use HAVING in your SQL statement, together with a GROUP BY. You seem to already know how to use itWhen you are in doubt, give us your best shot, and we will help you from there.

  12. #12
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by dinesh12 View Post
    hey u was totally out of track.
    It's all in the translation.

  13. #13
    Join Date
    Jan 2011
    Posts
    7

    Cool It's my mistake.

    Quote Originally Posted by corncrowe View Post
    It's all in the translation.
    K leave it..I'm not familiar with sql so i'm asking this sill qstn.. Now consider my table contains few more columns other than this PID , Partner_sku. I want to display all other columns relevant to this distinct PID ,partner_sku. can u give the query.

  14. #14
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Ok! But to be honest with you the design is flawed. Normalisation rules should prevent dupicated data in 1st normal form. There should probably be a primary key (composite) that does not allow the type of rows presented in your example. Each row should be unique. Why would you have many rows of the same data?

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM a
       WHERE 1 < (SELECT Count(*)
          FROM a AS b
          WHERE  b.c = a.c
             AND b.d = a.d)
    Should do exactly what you requested. However, your request has two different problems...

    First, you didn't post enough information for us to help you. If you'd like a more specific answer that you can cut-and-paste you'll have to give us at least the CREATE TABLE statement needed to build your table.

    More importantly, your question indicates that you have a larger problem. This is what corncrowe was getting at in his previoius post. This kind of problem can probably be prevented if you define your table better. If you post the CREATE TABLE statement, we might be able to help with that problem too. This is your larger problem, and it will almost certainly cause you more problems in the future.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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