Deleting double entries

odin568 · #1 (**permalink**) 09-14-11, 04:38

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!

tonkuma · #2 (**permalink**) 09-14-11, 07:06

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.

tonkuma · #3 (**permalink**) 09-14-11, 07:13

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.

odin568 · #4 (**permalink**) 09-14-11, 08:14

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech