Re: SQL Query help needed

On 2005-06-24, surenm_at_gmail.com <surenm_at_gmail.com> wrote:
> I have the following two tables (AA & BB) that I need to use to
> populate a third table (CC)
>
> Table AA
> item_code type rel_code
> 1_01 1
> 1_02 1
> 1_03 1
> 1_04 1
> 1_05 1
> 1_06 1
> 2_01 2 abc
> 2_02 2 bcd
> 2_03 2 cde
>
> Table BB
> rel_code item_code
> abc 1_01
> abc 1_02
> abc 1_04
> abc 1_06
> bcd 1_02
> bcd 1_05
> bcd 1_06
> cde 1_02
> cde 1_04
>
>>From these tables, I need to populate the third table
> Table CC
> item_code_1 item_code_2 priority
> 1_01 2_01 1
>
> 1_02 2_01 1
> 1_02 2_02 2
> 1_02 2_03 3
>
> 1_04 2_01 1
> 1_04 2_03 2
>
> 1_05 2_02 1
>
> 1_06 2_01 1
> 1_06 2_02 2
>
> Note that the priority field in table CC is a counter
>
> I got the select working that gives me item_code_1 and item_code_2 but
> I don't know how to set the
> priority field (counter) in the SQL.
>
> Any help will be appreciated.

Sure. Yet, I'd appreciate if you showed at least your 'working select' and create table, insert into statements.

Probably, you'll have to look into the
row_number() over (group by ... order by ...) analytic function.

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/