How to check for Uniqueness across Records in SQL

1

Hi I have table name Buyers which has three columns SR_NO,Buyer_ID,Buyer_Status
I want to create a new column where I can fetch Unique status across the Buyer_ID.

For Example Buyer_ID has 2222 has only "Yes" in Buyer_Status across each SR._NO related to it, so I want to print "Yes" in Unique_Status across all SR_NO of 2222, Similarly
Buyer ID 4444 has "No" across each SR_NO ,so "Yes" should be printed in Unique_Status column for all SR_NO of Buyer_ID 4444. so in other words if Buyer_ID has unique status in Buyer_Status Column then "Yes" should be printed in Buyer_Status across all its SR_NO otherwise Print "No"
QAA

2

hi hope this helps

jig saw puzzle number 100000003345678899 ( out of so many puzzles in life )

just joking

create table SampleData 
(
srno int identity(1,1), 
buy_id int , 
buy_st varchar(50), 
)

insert into SampleData select 1111,'Yes'
insert into SampleData select 1111,'No'
insert into SampleData select 1111,'No'

insert into SampleData select 2222,'Yes'
insert into SampleData select 2222,'Yes'
insert into SampleData select 2222,'Yes'
insert into SampleData select 2222,'Yes'

insert into SampleData select 3333,'No'

insert into SampleData select 4444,'No'
insert into SampleData select 4444,'No'



select 'Sample Data', * from SampleData

; with cte as 
(
select buy_id,count(distinct buy_st) as cnt  from SampleData group by buy_id having count(distinct buy_st) > 1 
) 
select a.srno,a.buy_id,a.buy_st,case when b.buy_id  is not null then 'No' else 'YES' end as Unique_Status from SampleData a left join cte b on a.buy_id = b.buy_id



drop table SampleData

image

3

Please provide directly usable data -- CREATE TABLE and INSERT statement(s) -- rather than a picture / "splat" of data. Remember, we don't have your data at all and can't run code against a picture.

The COUNT(DISTINCT) is inefficient for what needs done here, thus I suggest MIN() / MAX() instead.

CREATE TABLE #SampleData ( SR_NO int IDENTITY(1,1), 
    Buyer_ID int NOT NULL, Buyer_Status varchar(50) NOT NULL );
INSERT INTO #SampleData VALUES
    (1111,'Yes'), (1111,'No'),  (1111,'No'),
    (2222,'Yes'), (2222,'Yes'), (2222,'Yes'), (2222,'Yes'),
    (3333,'No'),  
    (4444,'No'),  (4444,'No')

;WITH cte_Buyer_IDs_with_more_than_one_st AS (
    SELECT Buyer_ID
    FROM #SampleData
    GROUP BY Buyer_ID
    HAVING MIN(Buyer_Status) <> MAX(Buyer_Status)
)
SELECT SD.SR_NO, SD.Buyer_ID, SD.Buyer_Status, 
    CASE WHEN cb.Buyer_ID IS NULL THEN 'Yes' ELSE 'No' END AS Unique_Status
FROM #SampleData SD
LEFT OUTER JOIN cte_Buyer_IDs_with_more_than_one_st cb ON 
    cb.Buyer_ID = SD.Buyer_ID