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

re: Unique values in column

Dawid Zolkiewicz wrote: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;

re: Unique values in column

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

re: Unique values in column

Here is link to that discussion

http://forums.devshed.com/db2-develo...nt-for-rownum-
of-oracle-162352.html

re: Unique values in column

"Dawid Zolkiewicz" <j_e_l_l_o_w@tenbit.plwrote in message
news:ge0qq1$7pc$1@aioe.org...Did you ever consider consulting the SQL Reference Manual to see if it is
supported on the DB2 platform and version you are using?

re: Unique values in column

"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
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.

re: Unique values in column

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

re: Unique values in column

Did you ever consider consulting the SQL Reference Manual to see if itI 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

re: Unique values in column

On Oct 25, 8:41*am, Dawid Zolkiewicz <j_e_l_l_...@tenbit.plwrote: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

re: Unique values in column

On Oct 25, 10:49*am, Ian <ianb...@mobileaudio.comwrote:Actually, the function is ROW_NUMBER() - you need the underbar. Of
course, it still may not work depending on version.

-Chris