Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    25

    Post Unanswered: Deleting double entries

    Hi,

    I have a problem with double entries in a query that I cannot fix.

    The table has one column "code". This is a varchar with special fixed formatting:

    1-8: Code-ID
    22-30: Host.

    In many queries I always work with the Code-ID using the code

    Code:
    substr(code,1,8) as Code-ID
    substr(code,22,8) as Host
    Now I want to have a list of all Code-IDs unique. I get it with this:

    Code:
    Select distinct(substr(code,1,8)) from summary
    For a dropdown Box, I now want to have a list with the following output:

    Code:
    |   Code-ID    |    (Code-ID+" - "+Host)     |
    I hope it is understandable what I mean inside the brackets. A concat of the id and the host with " - " between.

    That wouldn't also be that hard, but - and now the point:

    I really want the Code-Id to be unique, only one time in the result. Which host I then have in the column doesn't matter, any is good.

    The column code has several entries for ONE Code-ID with different hosts.

    This query mostly doesn't make sense, but in my case I need it :-)

    I tried it with several Joins (left inner) but I always get double results...

    This was one of my various tries:

    Code:
    With c(code) as
    (
    SELECT distinct(substr(code,1,8)) as code from summary
    order by code
    ),d(code, host) as
    (
    SELECT distinct(substr(code,1,8)) as code, substr(code,22,8) as host from summary
    )
    Select distinct(c.code), d.host from c left join d on c.code = d.code
    group by code, host
    ;
    Thank you!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It would be better to give sample data and expected results.

    Anyway, you may want to modify the following example to get what you want.

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     summary(code) AS (
    VALUES
      'aaaaaaaa.............11111111'
    , 'aaaaaaaa.............22222222'
    , 'aaaaaaaa.............33333333'
    , 'bbbbbbbb.............11111111'
    , 'bbbbbbbb.............22222222'
    , 'cccccccc.............22222222'
    , 'cccccccc.............33333333'
    , 'cccccccc.............55555555'
    , 'dddddddd.............33333333'
    , 'dddddddd.............44444444'
    )
    SELECT SUBSTR(code , 1 , 8)                   AS "Code-ID"
         , '(' || SUBSTR(code , 1 , 8) || '-'
           || MAX( SUBSTR(code , 22 , 8) ) || ')' AS "Code-ID + Host"
     FROM  summary
     GROUP BY
           SUBSTR(code , 1 , 8)
    ;
    ------------------------------------------------------------------------------
    
    Code-ID  Code-ID + Host     
    -------- -------------------
    aaaaaaaa (aaaaaaaa-33333333)
    bbbbbbbb (bbbbbbbb-22222222)
    cccccccc (cccccccc-55555555)
    dddddddd (dddddddd-44444444)
    
      4 record(s) selected.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that you may misunderstand the keyword distinct.

    "distinct" is applied whole columns in a select list.
    So, parentheses after distinct in your code are useless(or no effect).

    "Select distinct(c.code), d.host" is equivalent to "Select distinct c.code, d.host"
    and it returns unique combination of c.code and d.host.

  4. #4
    Join Date
    Aug 2011
    Posts
    25
    ah ok. I had a different comprehension from distinct, that was my problem over all my tries.
    Thank you a lot, this is exactly what I need. Sorry, I missed sample date.

Posting Permissions

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