Unique values in column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dawid Zolkiewicz - 25 Oct 2008 14:41 GMT

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:

+-----------+-----------+-----------+-----------+-----------+

+-----------+-----------+-----------+-----------+-----------+

| 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

Reply to this Message

Ian - 25 Oct 2008 18:49 GMT

> Hello
>
[quoted text clipped - 22 lines]
> 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;

Reply to this Message

Dawid Zolkiewicz - 26 Oct 2008 05:13 GMT

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

Reply to this Message

Dawid Zolkiewicz - 26 Oct 2008 05:23 GMT

Here is link to that discussion

http://forums.devshed.com/db2-development-114/db2-equivalant-for-rownum-
of-oracle-162352.html

Reply to this Message

Mark A - 26 Oct 2008 09:47 GMT

> For some reason I can't use rownumber - I have error:
>
[quoted text clipped - 5 lines]
>
> 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?

Reply to this Message

Dawid Zolkiewicz - 26 Oct 2008 17:24 GMT

> 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

Reply to this Message

ChrisC - 28 Oct 2008 16:20 GMT

> SELECT prsnID ,
> feature1,
[quoted text clipped - 13 lines]
>
> - Show quoted text -

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

-Chris

Reply to this Message

--CELKO-- - 26 Oct 2008 14:23 GMT

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

Reply to this Message

Dawid Zolkiewicz - 26 Oct 2008 17:15 GMT

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

+-----------+-------------------------+-------------------------+-------+

+-----------+-------------------------+-------------------------+-------+

+-----------+-------------------------+-------------------------+-------+

+-----------+-------------------------+-------------------------+-------+

+-----------+-------------------------+-------------------------+-------+

+-----------+-------------------------+-------------------------+-------+

+-----------+-------------------------+-------------------------+-------+

+-----------+-------------------------+-------------------------+-------+

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

Reply to this Message

Arun Srinivasan - 27 Oct 2008 07:11 GMT

> Hello
>
[quoted text clipped - 26 lines]
>
> 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

Reply to this Message