I have the following table:
| Claim#1 | Claim#2 |
|---|---|
| ABC | 123 |
| 123 | ABC |
| DEF | 456 |
| 789 | GHI |
I need to figure out how to eliminate one of the first 2 rows since it is pulling the same data only switched.
I have the following table:
| Claim#1 | Claim#2 |
|---|---|
| ABC | 123 |
| 123 | ABC |
| DEF | 456 |
| 789 | GHI |
I need to figure out how to eliminate one of the first 2 rows since it is pulling the same data only switched.
You may use a least/greatest trick here:
SELECT DISTINCT
CASE WHEN Claim1 < Claim2 THEN Claim1 ELSE Claim2 END AS Claim1,
CASE WHEN Claim1 < Claim2 THEN Claim2 ELSE Claim1 END AS Claim2
FROM yourTable;
If your SQL database happen to support the scalar LEAST() and GREATEST() functions, then we can simplify to:
SELECT DISTINCT
LEAST(Claim1, Claim2) AS Claim1,
GREATEST(Claim1, Claim2) AS Claim2
FROM yourTable;
c2.id is null) like (claim2,claim1)not c2.id is null) only select those where claim1 < claim2select c1.*
from claims c1
left join claims c2 on c2.claim1=c1.claim2 and c2.claim2=c1.claim1
where c2.id is null
or c1.claim1<c1.claim2 and not c2.id is null
I'm sure there are a few clever ways to do this, however I'm just going with the brute force way. Sure not optimized, but easy to understand and adjust as needed.
First we need to inner join to itself and get the values to remove, and then loop it and delete them one at a time.
IF OBJECT_ID('claims') IS NOT NULL
DROP TABLE claims
GO
CREATE TABLE [dbo].[claims](
[id] [int] IDENTITY(1,1) NOT NULL,
[claim1] [varchar](50) NULL,
[claim2] [varchar](50) NULL,
CONSTRAINT [PK_claims] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO claims VALUES
('ABC', '123'),
('123', 'ABC'),
('DEF', '456'),
('456', '555'),
('789', 'GHI'),
('111', 'AAA'),
('AAA', '111')
GO
DECLARE @CONTINUE INT = 1
WHILE @CONTINUE <> 0
BEGIN
DELETE TOP(1) FROM CLAIMS WHERE id IN (
SELECT a.id FROM CLAIMS a
JOIN CLAIMS b
ON a.claim1 = b.claim2
AND b.claim2 = a.claim1
)
SET @CONTINUE = @@ROWCOUNT
END
GO
SELECT * FROM claims