473,320 Members | 1,951 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.

Unique values in column

Hello

I'm new here, so at the beginning I'd like to say hello for everybody.

First I'll describe my problem.

There is db2 database :) with about 0.5 mln people. Every person has
three features. These features have allowable values and also are stored
in db. I have to prepare query to find all possible combinations ok there
features and for every combination find one person having them.

So far I have query to find combinations but I can’t limit number of
people to 1. My idea was to connect values from three columns to one, so
I have:

+-----------+-----------+-----------+-----------+-----------+
|feature 1 |feature 2 |feature 3 |1+2+3 |prsn ID |
+-----------+-----------+-----------+-----------+-----------+
| A | B | C | A/B/C | |
+-----------+-----------+-----------+-----------+-----------+
| D | E | F | D/E/F | |
+-----------+-----------+-----------+-----------+-----------+

And my idea was to force query to show in table only unique values in 4th
column. But I don't know how to do that. Unfortunately, for some reason
option ROW_NUMBER doesn't work.

I'll be very thankful for any ideas.

Dawid


Oct 25 '08 #1
9 5378
Ian
Dawid Zolkiewicz wrote:
Hello

I'm new here, so at the beginning I'd like to say hello for everybody.

First I'll describe my problem.

There is db2 database :) with about 0.5 mln people. Every person has
three features. These features have allowable values and also are stored
in db. I have to prepare query to find all possible combinations ok there
features and for every combination find one person having them.

So far I have query to find combinations but I can’t limit number of
people to 1. My idea was to connect values from three columns to one, so
I have:

+-----------+-----------+-----------+-----------+-----------+
|feature 1 |feature 2 |feature 3 |1+2+3 |prsn ID |
+-----------+-----------+-----------+-----------+-----------+
| A | B | C | A/B/C | |
+-----------+-----------+-----------+-----------+-----------+
| D | E | F | D/E/F | |
+-----------+-----------+-----------+-----------+-----------+

And my idea was to force query to show in table only unique values in 4th
column. But I don't know how to do that. Unfortunately, for some reason
option ROW_NUMBER doesn't work.
You don't say whether a person that has A/B/C is the same as a person
that has C/B/A, but let's assume they are different.

SELECT prsnID ,
feature1,
feature2,
feature3
FROM
(SELECT prsnID ,
feature1,
feature2,
feature3,
ROWNUMBER() OVER (PARTITION BY feature1,
feature2,
feature3) AS rn
FROM people
) AS p
WHERE rn = 1;
Oct 25 '08 #2
For some reason I can't use rownumber - I have error:

ERROR[42884][IBM][DB2] SQL0440N No autorized routine named "ROWNUMBER"
of type "" having compatible arguments was found.

I found information on some forum that option doesn't work in Mainframe
(I'm using that).

David
Oct 26 '08 #3
Here is link to that discussion

http://forums.devshed.com/db2-develo...nt-for-rownum-
of-oracle-162352.html
Oct 26 '08 #4
"Dawid Zolkiewicz" <j_*********@tenbit.plwrote in message
news:ge**********@aioe.org...
For some reason I can't use rownumber - I have error:

ERROR[42884][IBM][DB2] SQL0440N No autorized routine named "ROWNUMBER"
of type "" having compatible arguments was found.

I found information on some forum that option doesn't work in Mainframe
(I'm using that).

David
Did you ever consider consulting the SQL Reference Manual to see if it is
supported on the DB2 platform and version you are using?
Oct 26 '08 #5
"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html
>And my idea was to force query to show in table only unique values in 4th column. But I don't know how to do that. Unfortunately, for some reason option ROW_NUMBER doesn't work. <<
Your fourth column is not in First Normal Form and it is redundant.

This sounds like an OUTER JOIN on a three-way CROSS JOIN, but without
DDL, we cannot write a query.
Oct 26 '08 #6
Please post DDL,

I can't do that, because I don't have access to documentation like that
(I'm only little user), but I prepared schema of interesting tables and
expected result.

Feature1_dfn
+-----------+------------+
|Feature1_ID|Feature1_DSC|
+-----------+------------+
|INT |STR |
+-----------+------------+
|1 |plan1 |
+-----------+------------+
|2 |plan2 |
+-----------+------------+
|3 |plan3 |
+-----------+------------+

Feature2_3_dfn
+-------------+--------------------+
|Feature2_3_ID|Feature2_3_vld_value|
+-------------+--------------------+
|INT |STR |
+-------------+--------------------+
|1 |option1 |
+-------------+--------------------+
|1 |option2 |
+-------------+--------------------+
|1 |option3 |
+-------------+--------------------+
|1 |option4 |
+-------------+--------------------+
|2 |variant1 |
+-------------+--------------------+
|2 |variant2 |
+-------------+--------------------+
|2 |variant3 |
+-------------+--------------------+
|2 |variant4 |
+-------------+--------------------+

PRSN_FEATURE1
+--------+-----------+
|PRSN_ID |Feature1_ID|
+--------+-----------+
|INT |INT |
+--------+-----------+
|0001 |1 |
+--------+-----------+
|0002 |1 |
+--------+-----------+
|0002 |2 |
+--------+-----------+

PRSN_FEATURE2_3
+-------+------------------+---------------------+
|PRSN_ID|PRSN_FEATURE2_3_ID|PRSN_FEATURE2_3_VALUE|
+-------+------------------+---------------------+
|0001 |1 |option2 |
+-------+------------------+---------------------+
|0001 |2 |variant1 |
+-------+------------------+---------------------+
|0002 |1 |option3 |
+-------+------------------+---------------------+
|002 |2 |variant4 |
+-------+------------------+---------------------+

QUERY
+-----------+-------------------------+-------------------------+-------+
|Feature1_ID|FEATURE2_3_VALUE (ID = 1)|FEATURE2_3_VALUE (ID = 2)|PRSN_ID|
+-----------+-------------------------+-------------------------+-------+
|plan1 |option1 |variant1 | |
+-----------+-------------------------+-------------------------+-------+
|plan1 |option1 |variant2 | |
+-----------+-------------------------+-------------------------+-------+
|plan1 |option1 |variant3 | |
+-----------+-------------------------+-------------------------+-------+
|plan1 |option1 |variant4 | |
+-----------+-------------------------+-------------------------+-------+
|plan1 |option2 |variant1 | |
+-----------+-------------------------+-------------------------+-------+
|plan1 |option2 |variant2 | |
+-----------+-------------------------+-------------------------+-------+
|plan1 |option2 |variant3 | |
+-----------+-------------------------+-------------------------+-------+

Tables *_dfn collect allowable, valid values of these three features.

Tables PRSN_* collect informations about people

PRSN_IDs found in query don't have to be unique (usually one person has a
few plans, one option and one variant).

Found person can be random, first or last.

Problem is - possible combinations of feature is about 30.000 and
possible combinations of futures and people about 3.000.000.

Thank you for your help

Dawid
Oct 26 '08 #7
Did you ever consider consulting the SQL Reference Manual to see if it
is supported on the DB2 platform and version you are using?
I working remotely with mainframe and I couldn't determine what version
we are using. My co-worker said is probably 6th one. Is any option to
don't use ROWNUMBER?

Dawid
Oct 26 '08 #8
On Oct 25, 8:41*am, Dawid Zolkiewicz <j_e_l_l_...@tenbit.plwrote:
Hello

I'm new here, so at the beginning I'd like to say hello for everybody.

First I'll describe my problem.

There is db2 database :) with about 0.5 mln people. Every person has
three features. These features have allowable values and also are stored
in db. I have to prepare query to find all possible combinations ok there
features and for every combination find one person having them.

So far I have query to find combinations but I can’t limit number of
people to 1. My idea was to connect values from three columns to one, so
I have:

+-----------+-----------+-----------+-----------+-----------+
|feature 1 *|feature 2 *|feature 3 *|1+2+3 * * *|prsn ID * *|
+-----------+-----------+-----------+-----------+-----------+
| * * A * * | * * B * * | * * C * * | A/B/C * * | * * * * * |
+-----------+-----------+-----------+-----------+-----------+
| * * D * * | * * E * * | * * F * * | D/E/F * * | * * * * * |
+-----------+-----------+-----------+-----------+-----------+

And my idea was to force query to show in table only unique values in 4th
column. But I don't know how to do that. Unfortunately, for some reason
option ROW_NUMBER doesn't work.

I'll be very thankful for any ideas.

Dawid
The following is just a skeletal one to show how your query can be
outlined, please customize it to your need.
The table arun.people_test has 4 columns, name, q1,q2,q3.
db2 "select * from arun.people_test"

Q1 Q2 Q3 NAME
-- -- -- -------------------------
a a a arun
a b b sandy
a b b sandy1
a a a arun1

4 record(s) selected.

with temp1(q1,q2,q3) as
(select q1,q2,q3 from arun.people_test group by q1,q2,q3),
temp2(q1,q2,q3,name,r) as (select t.q1,t.q2,t.q3,p.name,row_number()
over(partition by t.q1,t.q2,t.q3) as r from temp1 t,arun.people_test
p where t.q1=p.q1 and t.q2=p.q2 and t.q3 = p.q3)
select * from temp2 where r=1
Q1 Q2 Q3 NAME R
-- -- -- ------------------------- --------------------
a a a arun 1
a b b sandy 1
Oct 27 '08 #9
On Oct 25, 10:49*am, Ian <ianb...@mobileaudio.comwrote:
SELECT prsnID *,
* * * * feature1,
* * * * feature2,
* * * * feature3
FROM
* * * * (SELECT prsnID ,
* * * * * * * *feature1,
* * * * * * * *feature2,
* * * * * * * *feature3,
* * * * * * * *ROWNUMBER() OVER (PARTITION BY feature1,
* * * * * * * * * * * * * * * * * * * * * * * feature2,
* * * * * * * * * * * * * * * * * * * * * * * feature3) AS rn
* * * * FROM * people
* * * * ) AS p
WHERE *rn = 1;- Hide quoted text -

- Show quoted text -
Actually, the function is ROW_NUMBER() - you need the underbar. Of
course, it still may not work depending on version.

-Chris
Oct 28 '08 #10

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

Similar topics

5
by: Westcoast Sheri | last post by:
Which will be a faster lookup of an item by, "color" in the following mySQL tables: "unique key," "primary key," or just plain "key"?? CREATE TABLE myTable ( number int(11) NOT NULL default '0',...
26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
3
by: uw_badgers | last post by:
Is it possible to create a unique constraint to a column from another table? For example: tb_current: current_names -------------- aaa bbb tb_new:
3
by: Prince Kumar | last post by:
Is there any way I can define an Unique constraint or unique index which allows more than one null values for the same column combination in DB2? ie, If my index is defined on (col3, col4) where...
2
by: Laurent | last post by:
DB2 8.1 ------- db2 => create table test (COL1 VARCHAR(10)) db2 => insert into test values ('A') db2 => insert into test values ('A ') db2 => insert into test values ('B') db2 => insert into...
5
by: Marie | last post by:
Access97 I have a table containing addresses with a separate field for State. Is there a way to create a query that returns an unique list of the states in that table and still be updateable? I...
11
by: sqlservernewbie | last post by:
Hi Everyone, Here is a theoretical, and definition question for you. In databases, we have: Relation a table with columns and rows
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.