473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Oct 28 '08 #1
7 4629
On 28 Okt, 17:25, swami <sivaswamim...@gmail.comwrote:
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.
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

Oct 28 '08 #2
On 28 Okt, 17:25, swami <sivaswamim...@gmail.comwrote:
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
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
Oct 28 '08 #3
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:
On 28 Okt, 17:25, swami <sivaswamim...@gmail.comwrote:
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

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
Oct 29 '08 #4
On Oct 29, 4:43*am, swami <sivaswamim...@gmail.comwrote:
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:
On 28 Okt, 17:25, swami <sivaswamim...@gmail.comwrote:
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
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

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
Oct 29 '08 #5
jefftyzzer wrote:
On Oct 29, 4:43Â*am, swami <sivaswamim...@gmail.comwrote:
>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:
On 28 Okt, 17:25, swami <sivaswamim...@gmail.comwrote:
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
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


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.
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 **
Oct 29 '08 #6
On Oct 29, 10:19*am, "Will Ho...@teranews.com" <who...@yahoo.com>
wrote:
jefftyzzer wrote:
On Oct 29, 4:43*am, swami <sivaswamim...@gmail.comwrote:
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:
On 28 Okt, 17:25, swami <sivaswamim...@gmail.comwrote:
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
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
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.

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 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
Oct 29 '08 #7
On Oct 29, 10:19*am, "Will Ho...@teranews.com" <who...@yahoo.com>
wrote:
jefftyzzer wrote:
On Oct 29, 4:43*am, swami <sivaswamim...@gmail.comwrote:
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:
On 28 Okt, 17:25, swami <sivaswamim...@gmail.comwrote:
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
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
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.

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 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
Oct 29 '08 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: gk | last post by:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">...
4
by: gurvar | last post by:
Hi I'm trying to remove duplicate elements from a Drop Down List Fill in VB.net. Following code worked well with vb6. But I'm getting index out of range if I try to translate it to vb.net code...
21
by: CSN | last post by:
I have a pretty simple select query that joins a table (p) with 125K rows with another table (pc) with almost one million rows: select p.* from product_categories pc inner join products p on...
16
by: tyrfboard | last post by:
I've been searching for awhile now on how to remove duplicates from a table within an Access db and have found plenty of articles on finding or deleting duplicates. All I want to do is remove them...
2
by: psbasha | last post by:
Hi, I have a duplicate numbers in a list.I would like to have unique numbers in the list . Say Input : Sample l =
3
by: Suyash Upadhyay | last post by:
How to find more than one duplicate elements in array? ( One method is BST, but I want efficient method than it)
10
by: flavourofbru | last post by:
Hi, vector<string> str; I have the vector filled with strings "a", "b", "c", "b", "c", "d", "a". i.e str = a; str = b; str = c;.....and so on. Now I would like to delete the duplicate...
5
by: kwartz | last post by:
Can somebody please help me with a select statement for selecting duplicate records with one different field . I have 3 tables: A, B and C A has 2 fields I ---- primary key m B
5
Fspinelli
by: Fspinelli | last post by:
I have an append query which takes data from a query and updates a table. However, instead of one record I get 3 or 4 of the same records dumped into the table. I might enter three records, press...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.