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 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
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
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
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
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 **
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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">...
|
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...
|
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...
|
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...
|
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 =
|
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)
|
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...
|
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
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
| |