Unique values in column

[Dawid Zolkiewicz]

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

[Ian]

Re: Unique values in column

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;

[Dawid Zolkiewicz]

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

[Dawid Zolkiewicz]

Re: Unique values in column

Here is link to that discussion

http://forums.devshed.com/db2-development-114/db2-equivalant-for-rownum-

of-oracle-162352.html

[Mark A]

Re: Unique values in column

"Dawid Zolkiewicz" <j_e_l_l_o_w@te nbit.plwrote in message
news:ge0qq1$7pc $1@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?

[--CELKO--]

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

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

[Dawid Zolkiewicz]

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|Fe ature1_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_F EATURE2_3_ID|PR SN_FEATURE2_3_V ALUE|
+-------+------------------+---------------------+
|0001 |1 |option2 |
+-------+------------------+---------------------+
|0001 |2 |variant1 |
+-------+------------------+---------------------+
|0002 |1 |option3 |
+-------+------------------+---------------------+
|002 |2 |variant4 |
+-------+------------------+---------------------+

QUERY
+-----------+-------------------------+-------------------------+-------+
|Feature1_ID|FE ATURE2_3_VALUE (ID = 1)|FEATURE2_3_V ALUE (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

[Dawid Zolkiewicz]

Re: Unique values in column

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

[Arun Srinivasan]

Re: Unique values in column

On Oct 25, 8:41 am, Dawid Zolkiewicz <j_e_l_l_...@te nbit.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_tes t has 4 columns, name, q1,q2,q3.
db2 "select * from arun.people_tes t"

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_tes t group by q1,q2,q3),
temp2(q1,q2,q3, name,r) as (select t.q1,t.q2,t.q3, p.name,row_numb er()
over(partition by t.q1,t.q2,t.q3) as r from temp1 t,arun.people_t est
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

[ChrisC]

Re: Unique values in column

On Oct 25, 10:49 am, Ian <ianb...@mobile audio.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