June 13, 2014 at 12:45 am
Dear master,
I have data below :
Id Calling
1004 6281100010
1298 624
1891 624
1000 624
0912 6281100010
8765 912
So i want the output only :
Id Calling
6281100010
624
8765 912
no matter which id is use , so only view data one value without any replacation, please any suggestion ?
thank you
June 13, 2014 at 1:13 am
This should get you started, two different methods.
😎
USE tempdb;
GO
DECLARE @TDATA TABLE
(
Id INT
,Calling VARCHAR(12)
);
INSERT INTO @TDATA
(
Id
,Calling
)
VALUES
(1004,'6281100010')
,(1298,'624')
,(1891,'624')
,(1000,'624')
,(0912,'6281100010')
,(8765,'912');
/* Window function method */
;WITH TDATA_DD AS
(
SELECT
TD.Id
,TD.Calling
,ROW_NUMBER() OVER
(
PARTITION BY TD.Calling
ORDER BY (SELECT NULL)
) AS DD_RID
FROM @TDATA TD
)
SELECT
TDD.Id
,TDD.Calling
FROM TDATA_DD TDD
WHERE TDD.DD_RID = 1
/* Aggregate method */
SELECT
MIN(TD.Id) AS Id
,TD.Calling
FROM @TDATA TD
GROUP BY TD.Calling;
Window function results
Id Calling
----------- ------------
1298 624
912 6281100010
8765 912
Aggregation method results
Id Calling
----------- ------------
1000 624
912 6281100010
8765 912
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply