Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Angry Unanswered: sql syntax for select duplicate data in same table

    hello, i am pretty messed up here, looking for a solution.
    here is the scenario....
    a table named A contains 4 columns (order_cd, id_no, row_cd, rate_rame)

    the table looks like this.
    order_cd id_no row_cd rate_rame
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    160 2144 1 BODY1
    160 2144 2 BODY
    160 2145 1 BODY1
    160 2145 2 BODY
    160 2146 1 BODY1
    160 2146 2 BODY
    160 2147 1 BODY1
    160 2147 2 BODY
    160 2148 1 BODY
    160 2149 1 BODY
    160 2149 2 BODY
    161 8589 1 BODY
    161 9249 1 BODY
    161 12581 1 BODY
    164 3 1 BODY
    164 8 1 BODY
    164 10 1 BODY

    i need to select rows which contains both rate_name with common order_cd and id_no.
    for example i want to select from start to this row from this table.
    160 2147 1 BODY
    please guys, help me out.
    regards

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Almaji, You will need to explain a bit more about what you want. What I can get from your post, you want the first set (defined by the 'breaks' I added) of the listed rows below (from start to this row 160 2147 1 BODY).

    However, the second set would 'fit' into any Where clause I can think of (other than ID_NO between 2144 and 1247).

    Code:
    order_cd id_no row_cd rate_name
    -------- ----- ------ ---------
    160       2144 1      BODY1
    160       2144 2      BODY
    160       2145 1      BODY1
    160       2145 2      BODY
    160       2146 1      BODY1
    160       2146 2      BODY
    160       2147 1      BODY1
    160       2147 2      BODY
    
    160       2148 1      BODY
    160       2149 1      BODY
    160       2149 2      BODY
    
    161       8589 1      BODY
    161       9249 1      BODY
    161      12581 1      BODY
    164          3 1      BODY
    164          8 1      BODY
    164         10 1      BODY
    What do you want to filter on in the Where clause to get the rows you are looking for?

  3. #3
    Join Date
    Aug 2010
    Posts
    6
    i am sorry i couldn't express my need and thank you for your interest for helping me. let me try again.
    let me explain the relations.
    order_cd (numbers of it)
    every order_cd contains hundreds of id_no starting from 1.
    not every but some id_no could be split in 2 as rowcd then their value is 1 and 2. if not split then its 1 always.
    rate_name is of 2 types... Body and Body1.
    there are some rows with different order_cd and id_no which rate name is Body and there are some rows with different order_cd and id_no which rate name is Body1 and
    but there are some rows with same Order_cd and same id_no but different rowcd (1 and 2) and different rate_name (Body and Body1).
    i want those rows with duplicate order_cd and duplicate id_no with different row_cd and different rate_name.
    there are some rows that has duplicate order_cd and duplicate id_no and different row_cd (1 and 2) but same rate_name. i dont want them.

    i need the result exactly as your first set.
    lets say i need couples of rows. every couple that has same rate_name and id_no but different rowcd and rate_name.
    let me explain it more.
    think of these simple query
    1 > select * from A where rate_name = 'Body'
    2 > select * from A where rate_name = 'Body1'
    i want to extract the rows where order_cd and id_no are common in both query and cut off everything else.

    i hope i am able to explain my problem now

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I think I got it. Try this

    Code:
    WITH TESTTAB (ORDER_CD, ID_NO, ROW_CD, RATE_NAME)
      AS (SELECT 160,       2144, 1,      'BODY1' UNION ALL
          SELECT 160,       2144, 2,      'BODY'  UNION ALL
          SELECT 160,       2145, 1,      'BODY1' UNION ALL
          SELECT 160,       2145, 2,      'BODY'  UNION ALL
          SELECT 160,       2146, 1,      'BODY1' UNION ALL
          SELECT 160,       2146, 2,      'BODY'  UNION ALL
          SELECT 160,       2147, 1,      'BODY1' UNION ALL
          SELECT 160,       2147, 2,      'BODY'  UNION ALL
          SELECT 160,       2148, 1,      'BODY'  UNION ALL
          SELECT 160,       2149, 1,      'BODY'  UNION ALL
          SELECT 160,       2149, 2,      'BODY'  UNION ALL
          SELECT 161,       8589, 1,      'BODY'  UNION ALL
          SELECT 161,       9249, 1,      'BODY'  UNION ALL
          SELECT 161,      12581, 1,      'BODY'  UNION ALL
          SELECT 164,          3, 1,      'BODY'  UNION ALL
          SELECT 164,          8, 1,      'BODY'  UNION ALL
          SELECT 164,         10, 1,      'BODY'  
         )
    SELECT *
    FROM TESTTAB A
    WHERE EXISTS (SELECT *
    FROM TESTTAB B
    WHERE A.ORDER_CD   = B.ORDER_CD
      AND A.ID_NO      = B.ID_NO
      AND A.ROW_CD    <> B.ROW_CD
      AND A.RATE_NAME <> B.RATE_NAME
    ) 
    ORDER BY ORDER_CD, ID_NO, ROW_CD
    
    ORDER_CD    ID_NO       ROW_CD      RATE_NAME
    ----------- ----------- ----------- ---------
            160        2144           1 BODY1    
            160        2144           2 BODY     
            160        2145           1 BODY1    
            160        2145           2 BODY     
            160        2146           1 BODY1    
            160        2146           2 BODY     
            160        2147           1 BODY1    
            160        2147           2 BODY     
    
      8 record(s) selected.

  5. #5
    Join Date
    Aug 2010
    Posts
    6
    thanks a lot bro. but the problem is i cant use any order_cd or id_no value as criteria as they are numerous.

    is there any other way?

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I am afraid you lost me again.

    If you can't use ORDER_CD and ID_NO, how do you expect to find anything?

    If the columns are Indexed, this should process efficiently.

    If you only want specifically ORDER_CD = 160, you can add that to your Where clause.

    If you only want so many rows returned you can use SELECT TOP x (where X is the number of rows you want returned).

  7. #7
    Join Date
    Aug 2010
    Posts
    6
    sorry bro.. looks like you i've totally failed to express my need.
    i dont need any certain number of rows or any certain order_cd.
    as i said... there are numbers of order_cd and i dont know which order_cd are duplicate. so, i cant use order_cd.
    the sample data i've provided was just a glimpse of an ocean. the table (lets say its like a library) contains hundreds of order_cd (lets say books), every order_cd contains thousands of id_no (its more like line numbers in a book) themselves.

    lets just forget everything that i said and stick to number 1 and 2 sql that i've stated earlier.
    can you plz show me a way how can i remove any rows that does not contain common order_cd and id_no...
    i want to intersect between 1 and 2.

  8. #8
    Join Date
    Aug 2010
    Posts
    6
    correction "sorry bro.. looks like i've totally failed to express my need."****

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I am not sure if any of these are still going to work for as they are just variations on the theme of my first SQL. The only difference is the hard coded value for RATE_NAME. However:

    Code:
    SELECT ORDER_CD, ID_NO, ROW_CD, RATE_NAME
    FROM TESTTAB A
    WHERE A.RATE_NAME = 'BODY'
      AND EXISTS (SELECT *
                  FROM TESTTAB B
                  WHERE B.RATE_NAME = 'BODY1'
                    AND A.ORDER_CD = B.ORDER_CD
                    AND A.ID_NO    = B.ID_NO
                 ) 
    
    RDER_CD    ID_NO       ROW_CD      RATE_NAME
    ----------- ----------- ----------- ---------
            160        2147           2 BODY     
            160        2146           2 BODY     
            160        2145           2 BODY     
            160        2144           2 BODY
    Or swap BODY and BODY1 around:

    Code:
    SELECT ORDER_CD, ID_NO, ROW_CD, RATE_NAME
    FROM TESTTAB A
    WHERE A.RATE_NAME = 'BODY1'
      AND EXISTS (SELECT *
                  FROM TESTTAB B
                  WHERE B.RATE_NAME = 'BODY'
                    AND A.ORDER_CD = B.ORDER_CD
                    AND A.ID_NO    = B.ID_NO
                 ) 
    
    ORDER_CD    ID_NO       ROW_CD      RATE_NAME
    ----------- ----------- ----------- ---------
            160        2147           1 BODY1    
            160        2146           1 BODY1    
            160        2145           1 BODY1    
            160        2144           1 BODY1
    Or this other variation may work for you:

    Code:
    SELECT ORDER_CD, ID_NO, RATE_NAME
    FROM TESTTAB A
    GROUP BY  ORDER_CD, ID_NO, RATE_NAME
    HAVING COUNT(*) = 1 AND MAX(ROW_CD) > 1
    ORDER BY  ORDER_CD, ID_NO, RATE_NAME
    
    ORDER_CD    ID_NO       RATE_NAME
    ----------- ----------- ---------
            160        2144 BODY     
            160        2145 BODY     
            160        2146 BODY     
            160        2147 BODY

  10. #10
    Join Date
    Aug 2010
    Posts
    6
    hello, thank you for your help. but its a bit complex for me to understand as i am very new to sql. i was trying these code that seems to solve my problem.
    can you please see it and let me know whether i am doing it right?

    select T1.order_cd, T1.id_no, T1.rate_name, T1.qty, T2.rate_name, T2.qty
    from
    (select * from tran_knitting_qty
    where rate_name = 'BODY1' and order_cd in (152, 160, 161, 203)) as T1
    inner join
    (select * from tran_knitting_qty where rate_name = 'Body' and order_cd in
    (152, 160, 161, 203)) as T2
    on T1.order_cd = T2. order_cd
    and T1.id_no = t2.id_no
    and t1.rate_name <> t2.rate_name

  11. #11
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    If your requirement is to have columns form both 'tables' on one row, then the Join you have will work. However, I would remove 'and t1.rate_name <> t2.rate_name' as it is not necessary. By definition you are joining one derived table with only BODY in it with another derived table that only has BODY1 in it. You would NEVER have any rows where they ARE equal. So a check for where they are Not Equal isn't necessary.
    Code:
    SELECT T1.ORDER_CD, T1.ID_NO, T1.RATE_NAME, T1.QTY, T2.RATE_NAME, T2.QTY
    FROM (SELECT *
          FROM TRAN_KNITTING_QTY
          WHERE RATE_NAME = 'BODY1'
            AND ORDER_CD IN(152, 160, 161, 203)
         ) AS T1
            INNER JOIN
         (SELECT *
          FROM TRAN_KNITTING_QTY
          WHERE RATE_NAME = 'BODY'
            AND ORDER_CD IN(152, 160, 161, 203)
         ) AS T2
            ON     T1.ORDER_CD   = T2.ORDER_CD
               AND T1.ID_NO      = T2.ID_NO
               AND T1.RATE_NAME <> T2.RATE_NAME
    However, the join will probably not perform as well as the Exists I first suggested. In that case you would get both rows but as separate rows.

    It is possible you can use the EXISTS query with some CASE expressions and a Group by to get the same results. You can try it to see if it performs better or not.
    Code:
    SELECT ORDER_CD
         , ID_NO
         , MAX(CASE ROW_CD WHEN 1 THEN RATE_NAME END) AS RATE_NAME_1
         , MAX(CASE ROW_CD WHEN 1 THEN ROW_CD    END) AS ROW_CD_1
         , MAX(CASE ROW_CD WHEN 2 THEN RATE_NAME END) AS RATE_NAME_2
         , MAX(CASE ROW_CD WHEN 2 THEN ROW_CD    END) AS ROW_CD_2
    FROM TESTTAB A
    WHERE ORDER_CD IN (152, 160, 161, 203)
      AND EXISTS (SELECT *
                  FROM TESTTAB B
                  WHERE A.ORDER_CD   = B.ORDER_CD
                    AND A.ID_NO      = B.ID_NO
                    AND A.ROW_CD    <> B.ROW_CD
                    AND A.RATE_NAME <> B.RATE_NAME
                  ) 
    GROUP BY ORDER_CD, ID_NO
    ORDER BY ORDER_CD, ID_NO
    
    ORDER_CD    ID_NO       RATE_NAME_1 ROW_CD_1    RATE_NAME_2 ROW_CD_2   
    ----------- ----------- ----------- ----------- ----------- -----------
            160        2144 BODY1                 1 BODY                  2
            160        2145 BODY1                 1 BODY                  2
            160        2146 BODY1                 1 BODY                  2
            160        2147 BODY1                 1 BODY                  2
    NOTE: You have QTY in your last query but didn't supply data. I am substituting ROW_CD in its place but the basic concept is still sound.

Tags for this Thread

Posting Permissions

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