Using CASE for a specific situation - How TO

Question

I'm trying to find the proper SQL for the following situation:

Supposed we have two tables:

TABLE A
ID int,
TEXT varchar(200)

TABLE B
ID int,
A_NO int,
B_NO int

Fields named "ID" on both tables can be join to link tables.

The following SQL:

SELECT
 A.ID,
 B.A_NO,
 B.B_NO
FROM
 A
LEFT JOIN
 B
ON A.ID = B.ID
ORDER BY A.ID, B.A_NO, B.B_NO

gives the following results:

Now, the problem. What is asked for is to have in the column B_NO a value = 1 for the MIN value of column A_NO and a value = 0 for all the others row with the same A_NO value. The results below are expected:

Please note that, in this example, we can find two rows for each B_NO value but it is possible to have more than 2 rows.

I have tried to reproduce these results by using a CASE but with no success. Thanks for you help in advance, Bouzouki.

Answer 1

something like

    select ID, a_no, b_no, 
           case when a_no = min_a_no then b_no else 0 end as new_b_no
    from 
           a left join b on a.id = b.id left join
           (Select ID, B_no, min(a_no) as min_a_no
            from  a left join b on a.id = b.id
            group by id, b_no) m on a.id = m.id and b.b_no = m.b_no
    ORDER BY A.ID, B.A_NO

Answer 2

Try this using CTE and ROW_NUMBER(); (DEMO)

Please note: I have considered myT as your joined query of A and B tables for demo purpose. So replace myT with as yours A LEFT JOIN B ON A.ID = B.ID.

;with cte as (
  select id, a_no, b_no, 
         row_number() over(partition by id,b_no order by a_no) rn
  from myT
)
select id,a_no, case when rn=1 then b_no else 0 end b_no
from cte
order by a_no

--RESULTS FROM DEMO TABLE
|      ID | A_NO | B_NO |
-------------------------
| 1031014 |    1 |    1 |
| 1031014 |    2 |    0 |
| 1031014 |    3 |    2 |
| 1031014 |    4 |    0 |
| 1031014 |    5 |    3 |
| 1031014 |    6 |    0 |
| 1031014 |    7 |    4 |
| 1031014 |    8 |    0 |
| 1031014 |    9 |    5 |
| 1031014 |   10 |    0 |