Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2008
    Posts
    10

    Unanswered: atleast one occurence of the record

    Hi,
    I have 322 records of which 20 records have one column same which make them duplicate records for me so i want to get the total 302 records which means the records .

    i cant use like select count(*),col_name from table_name
    GROUP BY col_name
    having count(*) >1; as it would eleminate the occurance of the record.

    ANy suggestion please.

    Thanks
    -R

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is this an answer?
    select DISTINCT col_name from table_name;

  3. #3
    Join Date
    Dec 2008
    Posts
    10
    No that doesn't solve my purpose as the entire record is not duplicate only some columns are and so it would be a distinct record anyways.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm not well at English, so I couldn't understand your requirements fully.
    Could you provide some sample data and required results?

  5. #5
    Join Date
    Dec 2008
    Posts
    10
    I have records this way..
    cola colb colc
    5710 123 abc00
    5710 123 def00
    5720 345 abc00
    5720 345 def00


    and i want to get

    cola colb colc
    5710 123 abc00
    5720 345 abc00

    and my preference is based on cola and i cant make any change to the data i just have execute privs.

    Hope this makes it clear

    Thanks
    -R

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ramanay
    I have records this way..
    cola colb colc
    5710 123 abc00
    5710 123 def00
    5720 345 abc00
    5720 345 def00


    and i want to get

    cola colb colc
    5710 123 abc00
    5720 345 abc00

    and my preference is based on cola and i cant make any change to the data i just have execute privs.

    Hope this makes it clear
    No it does not. Why do you choose "5710 123 abc00" and not "5710 123 def00"?
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Dec 2008
    Posts
    10
    when i have a single record based on cola it wud have abc00 or def00 for colc so its ok but when i have 2 records one with abc00 and one with def00 for the same value of cola it is supposed to be the one with abc00( and i just have these 2 values for colc )

    Can i use like rowid and rowid+1 and chk the cola and retrieve the first value of cola.
    I'm confused how to go ahead on this


    Thanks
    -R

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    select cola, colb, min(colc) from yourtable group by cola, colb
    Since you did not post the actual requirements, you will have to adapt the above to your data yourself.

    PS. Please refrain from using lolspeak on this board - some people find it not just annoying but disrespectful.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Nick is right (not only on the lolspeak). You need to do some sort of aggregation. The values in your column in question define the groups and in each group you have to figure out which record to choose for the result set.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Dec 2008
    Posts
    10
    I joined this forum just today..i dont know what lol speak is but i think its about the dummy data i provided..sorry if thats against the rules..thanks for your help

    -R

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    "lolspeak" is the "language" commonly used in text messaging, where you substitute abbreviations in place of the regular words, don't use punctuation marks or proper capitalization, e.g.

    when i have a single record based on cola it wud have
    Can i use like rowid and rowid+1 and chk the cola
    It may be faster to type, which I doubt, but it's quite difficult to read.
    Last edited by n_i; 12-16-08 at 19:17.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    To make your requirements clearer(for me), you can add more rows for your example.

    For these rows of data.
    cola colb colc
    5710 123 abc00
    5710 123 def00
    5710 135 abc00
    5710 135 aaa00
    5720 345 abc00
    5720 345 def00
    5720 111 ghi00

    What do you want to get?

    cola colb colc
    5710 123 abc00 -- this?
    5710 135 aaa00 -- or this?, or both?
    5720 ??? ?????
    Last edited by tonkuma; 12-16-08 at 23:15.

  13. #13
    Join Date
    May 2006
    Posts
    16
    Try something like this:
    (COLA is the col with duplicated values)
    SELECT TBLX.COLA, TBLX.COLB, TBLX.COLC, ...
    FROM ( SELECT TBL1.*, ROW_NUMBER() OVER () RID
    FROM TBL1 ) TBLX
    WHERE TBLX.RID = (SELECT MIN(B.RID) FROM TBLX A
    WHERE A.COLA = TBLX.COLA)

    Saludos, arlf.

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or a bit simpler, in case you want to see just one row per value of cola:
    Code:
    WITH t AS
    (SELECT cola, MIN(colb) AS colb
     FROM   table_name
     GROUP BY cola)
    SELECT t.cola, t.colb, MIN(x.colc)
    FROM   t INNER JOIN table_name x ON t.cola = x.cola AND t.colb = x.colb
    GROUP BY t.cola, t.colb
    (See also Nick's post from Tuesday for a yet simpler solution, in case you want to see one row per (cola, colb) combination.)
    Last edited by Peter.Vanroose; 12-18-08 at 04:42.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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