Unique values in column

External Since: Oct 25, 2008 Posts: 5

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 Smile

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

External Since: Sep 15, 2003 Posts: 225

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 Smile

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;