Query for selecting NON DUPLICATE ELEMENTS in a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

swami - 28 Oct 2008 17:25 GMT

What is the query for selecting non duplicate elements

for eg:

no name age
1 siva 28
2 blair 32
3 mano 28

i want to select blair which hasn't got any duplicate elements in age
column.

Thx in advance

Reply to this Message

Lennart - 28 Oct 2008 21:02 GMT

> What is the query for selecting non duplicate elements
>
[quoted text clipped - 7 lines]
> i want to select blair which hasn't got any duplicate elements in age
> column.

One possibility:

db2 "with T (no, name, age) as (values (1,'siva',28), (2,'blair',32),
(3,'mano',28)) select T.* from T where age in (select age from T group
by age having count(1) = 1)"

NO NAME AGE
----------- ----- -----------
2 blair 32

1 record(s) selected.

/Lennart

Reply to this Message

Lennart - 28 Oct 2008 21:16 GMT

> What is the query for selecting non duplicate elements
>
[quoted text clipped - 9 lines]
>
> Thx in advance

Not sure what happened to my post, here is one attempt:

db2 "with T (no, name, age) as (values (1,'siva',28), (2,'blair',32),
(3,'mano',28)) select T.* from T where age in (select age from T group
by age having count(1) = 1)"

NO NAME AGE
----------- ----- -----------
2 blair 32

1 record(s) selected.

/Lennart

Reply to this Message

lenygold - 29 Oct 2008 02:34 GMT

Slightly different solution:
with T1 (no, name, age) as
(values (1,'siva',28),
(2,'blair',32),
(3,'mano',28),
(4,'jano',27))
Select * from T1 A
where not exists(select ' ' from T1 B where a.AGE=b.AGE having count(*) > 1)
;
O NAME AGE
----------- --------- -----------
2 blair 32
4 jano 27

2 record(s) selected.

>> What is the query for selecting non duplicate elements
>>
[quoted text clipped - 15 lines]
>
>/Lennart

Reply to this Message

swami - 29 Oct 2008 12:43 GMT

Is this going to be such complex for retrieval of simple data
isn't there any function for this ? just like distinct ?
or can it be added to newer versions of db2 ?

> > What is the query for selecting non duplicate elements
>
[quoted text clipped - 23 lines]
>
> /Lennart

Reply to this Message

jefftyzzer - 29 Oct 2008 18:07 GMT

> Is this going to be such complex for retrieval of simple data
> isn't there any function for this ? just like distinct ?
[quoted text clipped - 27 lines]
>
> > /Lennart

Another way:

SELECT
NO,
NAME,
AGE
FROM
(
SELECT
NO,
NAME,
AGE,
ROW_NUMBER() OVER (PARTITION BY AGE) RN
FROM
DEMOG_TABLE
) DT
WHERE
RN = 1;

DISTINCT won't work (at least in a straightforward way) because you
can't use it to simultaneously retrieve several columns and apply the
DISTINCT on just a subset of them.

--Jeff

Reply to this Message

Will Honea@teranews.com - 29 Oct 2008 18:19 GMT

>> Is this going to be such complex for retrieval of simple data
>> isn't there any function for this ? just like distinct ?
[quoted text clipped - 50 lines]
> can't use it to simultaneously retrieve several columns and apply the
> DISTINCT on just a subset of them.

Your solution runs a good deal faster here - I've used both to update a
database I maintain for a non-profit group membership list. It's amazing
how many duplicate names appear in successive generations attending one
school

Signature

Will Honea
** Posted from http://www.teranews.com **

Reply to this Message

jefftyzzer - 29 Oct 2008 19:58 GMT

On Oct 29, 10:19 am, "Will Ho...@teranews.com" <who...@yahoo.com>
wrote:

> >> Is this going to be such complex for retrieval of simple data
> >> isn't there any function for this ? just like distinct ?
[quoted text clipped - 59 lines]
> Will Honea
> ** Posted fromhttp://www.teranews.com**

Swami:

It occurs to me that I may not have answered the right question. If
what you want is the first instance, duplicated or otherwise, then you
can use the query I originally posted. If, however, what you want is
to retrieve just those rows that are not/never were duplicated over
the age column, then this query is likely what you're after:

SELECT
NO,
NAME,
AGE
FROM
(
SELECT
NO,
NAME,
AGE,
COUNT() OVER (PARTITION BY AGE) CNT
FROM
DEMOG_TABLE
) DT
WHERE
CNT = 1;

Will: thanks for your comment. Glad to know you're doing your part to
ensure high-quality data!

Regards,

--Jeff

Reply to this Message

jefftyzzer - 29 Oct 2008 20:18 GMT

On Oct 29, 10:19 am, "Will Ho...@teranews.com" <who...@yahoo.com>
wrote:

Swami:

It occurs to me that I may not have answered the right question. If
what you want is the first instance, duplicated or otherwise, then you
can use the query I originally posted. If, however, what you want is
to retrieve just those rows that are not/never were duplicated over
the age column, then this query is likely what you're after:

SELECT
NO,
NAME,
AGE
FROM
(
SELECT
NO,
NAME,
AGE,
COUNT(*) OVER (PARTITION BY AGE) CNT
FROM
DEMOG_TABLE
) DT
WHERE
CNT = 1;

Will:

Thanks for your comment. Glad to know you're doing your part to
ensure high-quality data!

Regards,

--Jeff

Reply to this Message