Eliminate duplicate Rows in Select statement in SQL Server

Last Reply one year ago By pandeyism

Posted one year ago

Hi

How I can get below result by removing the duplicates.

Price

Code

PriceNow

10

AA

3

15

BB

7

4

CC

1

DECLARE @Person AS TABLE(Id INT,Name VARCHAR(10))
INSERT INTO @Person VALUES(1,'Rustam')
INSERT INTO @Person VALUES(2,'Asror')

DECLARE @mainTable AS TABLE(Id INT,PersonId int,Price INT, Code VARCHAR(2))
INSERT INTO @mainTable VALUES(1,1,10,'AA')
INSERT INTO @mainTable VALUES(2,1,15,'BB')
INSERT INTO @mainTable VALUES(3,1,4,'CC')
INSERT INTO @mainTable VALUES(4,2,10,'AA')
INSERT INTO @mainTable VALUES(5,2,17,'BB')
INSERT INTO @mainTable VALUES(6,2,4,'CC')

DECLARE @Detail AS TABLE(Id INT, PersonId int,Price INT, PriceNow FLOAT,Code VARCHAR(2))
INSERT INTO @Detail VALUES(1,1,9, 4,'AA')
INSERT INTO @Detail VALUES(2,1,8, 0,'AA')
INSERT INTO @Detail VALUES(3,1,7, 0,'AA')
INSERT INTO @Detail VALUES(4,1,6, 3,'AA')
INSERT INTO @Detail VALUES(6,1,15, 0,'BB')
INSERT INTO @Detail VALUES(7,1,14, 0,'BB')
INSERT INTO @Detail VALUES(8,1,13, 7,'BB')
INSERT INTO @Detail VALUES(9,2,4, 0,'CC')
INSERT INTO @Detail VALUES(10,2,5, 2,'CC')
INSERT INTO @Detail VALUES(11,1,7, 1,'CC')

select m.Price, d.Code, (select top 1 CASE WHEN x.Price <> 0 AND x.PriceNow = 0 THEN x.Price
     WHEN x.Price <> 0 AND x.PriceNow <> 0 THEN x.PriceNow
END FROM @Detail x WHERE m.Code = x.Code ORDER BY x.Id DESC) as PriceNow from @mainTable m
inner join @Detail d on m.Code = d.Code 
join @Person p on p.Id = d.PersonId where m.PersonId = 1 

 

You are viewing reply posted by: pandeyism one year ago.
Posted one year ago

Hi PRA,

Refer below query.

you need to add distinct keyword to avoid duplicate records.

SQL

DECLARE @Person AS TABLE(Id INT,Name VARCHAR(10))
INSERT INTO @Person VALUES(1,'Rustam')
INSERT INTO @Person VALUES(2,'Asror')
 
DECLARE @mainTable AS TABLE(Id INT,PersonId int,Price INT, Code VARCHAR(2))
INSERT INTO @mainTable VALUES(1,1,10,'AA')
INSERT INTO @mainTable VALUES(2,1,15,'BB')
INSERT INTO @mainTable VALUES(3,1,4,'CC')
INSERT INTO @mainTable VALUES(4,2,10,'AA')
INSERT INTO @mainTable VALUES(5,2,17,'BB')
INSERT INTO @mainTable VALUES(6,2,4,'CC')
 
DECLARE @Detail AS TABLE(Id INT, PersonId int,Price INT, PriceNow FLOAT,Code VARCHAR(2))
INSERT INTO @Detail VALUES(1,1,9, 4,'AA')
INSERT INTO @Detail VALUES(2,1,8, 0,'AA')
INSERT INTO @Detail VALUES(3,1,7, 0,'AA')
INSERT INTO @Detail VALUES(4,1,6, 3,'AA')
INSERT INTO @Detail VALUES(6,1,15, 0,'BB')
INSERT INTO @Detail VALUES(7,1,14, 0,'BB')
INSERT INTO @Detail VALUES(8,1,13, 7,'BB')
INSERT INTO @Detail VALUES(9,2,4, 0,'CC')
INSERT INTO @Detail VALUES(10,2,5, 2,'CC')
INSERT INTO @Detail VALUES(11,1,7, 1,'CC')
 
SELECT DISTINCT m.Price, d.Code, (SELECT TOP 1 CASE WHEN x.Price <> 0 AND x.PriceNow = 0 THEN x.Price
     WHEN x.Price <> 0 AND x.PriceNow <> 0 THEN x.PriceNow
END FROM @Detail x WHERE m.Code = x.Code ORDER BY x.Id DESC) AS PriceNow FROM @mainTable m
INNER JOIN @Detail d ON m.Code = d.Code
JOIN @Person p ON p.Id = d.PersonId WHERE m.PersonId = 1

Output

Price Code PriceNow
4 CC 1
10 AA 3
15 BB 7