In SQL I Want Result Like

Question

Select * from CompanyName

Field1
A
A
A
B
B
C
D
E
E
E
E

This Is My Output I Want To Add 1 In Last Same Filed Like

Field1 Filed2
A       0
A       0
A       1
B       0
B       1
C       1
D       1
E       0
E       0
E       0
E       1

Answer 1

Since there is no guarantee that select * from table shows the results in the same order always, I would first add a generic row number and then use it for ranking

with temp1 as (
select
row_number() over (order by field1) as rn,
field1
from your_table),

/* Use the above generic row_number to add logic for your 0/1 field */
temp2 as (
select field1,
row_number() over (partition by field1 order by rn desc) as use_for_ranking
from temp1)

/* Using the above results, convert the ranks to 0/1 */
select field1,
case when (use_for_ranking) = 1 then 1 else 0 end as field2
from temp2

Answer 2

Another solution is to use LEAD:

create table t (Field1 char(1) not null);
insert into t (Field1)
values ('A'),('A'),('A')
     , ('B'),('B')
     , ('C')
     , ('D')
     , ('E'), ('E'), ('E'), ('E'), ('E');

select Field1, Field2
from (
   select Field1
     , case when lead(Field1) over (order by Field1) <> Field1 then 1 else 0 end as Field2
   from t
) as tt
order by Field1, Field2;


field1  field2
A   0
A   0
A   1
B   0
B   1
C   1
D   1
E   0
E   0
E   0
…
10 rows of 12

EDIT: to handle the last row where LEAD evaluates to null, COALESCE with an "impossible" token can be used:

select Field1, Field2
from (
   select Field1
     , case when coalesce(lead(Field1) over (order by Field1),'x') <> Field1 
            then 1 
            else 0 
       end as Field2
   from t
) as tt
order by Field1, Field2;

) as tt order by Field1, Field2;

field1  field2
A   0
A   0
A   1
B   0
B   1
C   1
D   1
E   0
E   0
E   0
E   0
E   1
12 rows

Another way to handle that is to swap the case and check for equality:

select Field1, Field2
from (
   select Field1
     , case when lead(Field1) over (order by Field1) = Field1 
            then 0 
            else 1 
       end as Field2
   from t
) as tt
order by Field1, Field2;

Fiddle