In SQL I Want Result Like-CodePudding

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

CodePudding user response：

The following solution is appropriate for the question asked:

select Field1
      ,case when Field2<>1 then 0 else 1 end as Field2 
from 
    (select Field1
           ,ROW_NUMBER() over (partition by Field1 order by Field1) as Field2 from T1
    )u
 order by Field1 asc ,Field2

Output :

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

Just keep in mind that this is not a good solution for big tables and operational data in your database

CodePudding user response：

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

CodePudding user response：

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