Auryn
New User
Joined: 11 Jan 2006 Posts: 83 Location: Lower Saxony (DE)
|
|
|
|
Hi there,
a new question:
Background: We have a table with some more than tree columns but with a pk column (let's call it PK_COL), a fk column (FK_COL) and third column with depending values (VAL_COL) which may contain duplicate values depending to one fk.
Sample:
Code: |
PK_COL FK_COL VAL_COL
01 01 Alpha
02 01 Bravo
03 01 Alpha
04 01 Charlie
05 01 Bravo
06 01 Alpha |
Thanks to all for your hints
The target is to SELECT DISTINCT FK_COL and VAL_COL but numbered by ROW_NUMBER.
I've gut a solution wit a sub-select
Code: |
select FK_COL
, VAL_COL
, row_number()
over (partition by FK_COL
order by FK_COL
, VAL_COL) RNUM
from (select distinct
FK_COL
, VAL_COL
from MY_DEP_TABLE
)
with ur
;
|
But I am looking for a 'leaner' select. Is there a posibility to combine the DISTINCT clause with the row numbering?
When I was trying
Code: |
select distinct
FK_COL
, VAL_COL
, row_number()
over (partition by FK_COL
order by FK_COL
, VAL_COL) RNUM |
the row numbers were interpreted as distinct values so the result containt duplicates :-( |
|
Auryn
New User
Joined: 11 Jan 2006 Posts: 83 Location: Lower Saxony (DE)
|
|
|
|
More than 100 views (including my own) - Thanks for viewing and thinking about it. I appreciate that.
But: I've managed this question this morning: Just don't use the DISTINCT but the GROUP BY clause...
Code: |
select FK_COL
, VAL_COL
, row_number()
over (partition by FK_COL) RNUM
from MY_DEP_TABLE
group by FK_COL
, VAL_COL |
|
|