SQL query to find first record of each customer and update

Question

I have a table where I have two columns i.e customer, project_id.. My requirement is to find for each customer what is the first project_id, and correspondingly name it like 'General Project' and if there are more than one projects for customer found then name the next project as concat('PROJECT', '-', project_id). In the picture below you can see the 4th column data is the desired result for my requirement, but I am not sure how to find this out? thanks in advance

Answer 1

Here's one option (which works in Oracle; can't tell about SQL Server). Sample data (you already have & don't type it is in lines #1 - 13. Query you might be interested in begins at line #15.

SQL> with
  2  test (row_num, customer, project_Id) as
  3    -- sample data
  4    (select 1, 115432, 1 from dual union all
  5     select 2, 115432, 2 from dual union all
  6     select 3, 115432, 3 from dual union all
  7     --
  8     select 4, 116500, 1 from dual union all
  9     select 5, 116500, 2 from dual union all
 10     --
 11     select 6, 112342, 3 from dual union all
 12     select 7, 112342, 4 from dual
 13    ),
 14  --
 15  data as
 16    (select row_num, customer, project_id,
 17            row_number() over (partition by customer order by project_id) rn
 18     from test
 19    )
 20  -- final query
 21  select row_num, customer, project_id,
 22    --
 23    case when rn = 1 then 'GENERAL PROJECT'
 24         else 'PROJECT-' || project_id
 25    end as project_name
 26  from data
 27  order by row_Num;

   ROW_NUM   CUSTOMER PROJECT_ID PROJECT_NAME
---------- ---------- ---------- -------------------------
         1     115432          1 GENERAL PROJECT
         2     115432          2 PROJECT-2
         3     115432          3 PROJECT-3
         4     116500          1 GENERAL PROJECT
         5     116500          2 PROJECT-2
         6     112342          3 GENERAL PROJECT
         7     112342          4 PROJECT-4

7 rows selected.