Query for selecting NON DUPLICATE ELEMENTS in a table

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

re: Query for selecting NON DUPLICATE ELEMENTS in a table

On 28 Okt, 17:25, swami <sivaswamim...@gmail.comwrote: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

re: Query for selecting NON DUPLICATE ELEMENTS in a table

On 28 Okt, 17:25, swami <sivaswamim...@gmail.comwrote: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

re: Query for selecting NON DUPLICATE ELEMENTS in a table

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 ?

On Oct 28, 8:16*pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:

re: Query for selecting NON DUPLICATE ELEMENTS in a table

On Oct 29, 4:43*am, swami <sivaswamim...@gmail.comwrote: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

re: Query for selecting NON DUPLICATE ELEMENTS in a table

jefftyzzer wrote:
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

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

re: Query for selecting NON DUPLICATE ELEMENTS in a table

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

re: Query for selecting NON DUPLICATE ELEMENTS in a table

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