October 16, 2015 at 6:18 pm
Hello,
would someone help me to generate output as specified below. i need to get 1st value when record is duplicated in hierarchyval column
IF OBJECT_ID('tempdb..#test') IS NOT NULL
drop table #test
create table #test
(
hierarchyid int
,hierarchyval int
)
insert into #test
values (1 ,30005)
, (2 ,30005)
,(3 ,30005)
,(4 ,30031)
,(5 ,30031)
,(6 ,30031)
,(7 ,30031)
,(8 ,30033)
,(9 ,30037)
,(10 ,30086)
,(11 ,30086)
,(12 ,30086)
,(13 ,30086)
,(14 ,30086)
,(15 ,30156)
,(16 ,30156)
,(17 ,30400)
,(18 ,30400)
,(19 ,30402)
,(20 ,30402)
,(21 ,30403)
,(22 ,30403)
,(23 ,31194)
,(24 ,31194)
,(25 ,31194)
,(26 ,31194)
,(27 ,82832)
,(28 ,82832)
,(29 ,82832 )
o/p should be as below
select * from #test
where hierarchyid in (
1,4,8,9,10,15,17,19,21,23,28)
October 16, 2015 at 7:00 pm
This should do it:
with cte as
(
Select hierarchyid, hierarchyval,
row_number() over(Partition by hierarchyval order by hierarchyid) RowNum
from #test
)
select * from cte
where rownum = 1
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2015 at 10:46 pm
I like the use of CTE but could you not get the same result by simply using:
select min(hierarchyid) hierarchyid, hierarchyval from #test
group by hierarchyval
Just seems a tad easier?
...
October 16, 2015 at 11:04 pm
Another quick solution
😎
SELECT
MIN(T.hierarchyid) AS [hierarchyid]
,T.hierarchyval
FROM #test T
GROUP BY T.hierarchyval;
The two solutions posted are close in performance or rather lack there of, if no supporting indices exist, i.e. Partition-Order-Covering or GroupBy-Order-Covering, see the example below.
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#test') IS NOT NULL
drop table #test
create table #test
(
hierarchyid int NOT NULL
,hierarchyval int NOT NULL
);
insert into #test (hierarchyid,hierarchyval)
values ( 1 ,30005)
,( 2 ,30005)
,( 3 ,30005)
,( 4 ,30031)
,( 5 ,30031)
,( 6 ,30031)
,( 7 ,30031)
,( 8 ,30033)
,( 9 ,30037)
,(10 ,30086)
,(11 ,30086)
,(12 ,30086)
,(13 ,30086)
,(14 ,30086)
,(15 ,30156)
,(16 ,30156)
,(17 ,30400)
,(18 ,30400)
,(19 ,30402)
,(20 ,30402)
,(21 ,30403)
,(22 ,30403)
,(23 ,31194)
,(24 ,31194)
,(25 ,31194)
,(26 ,31194)
,(27 ,82832)
,(28 ,82832)
,(29 ,82832);
/* Heap */
SELECT
MIN(T.hierarchyid) AS [hierarchyid]
,T.hierarchyval
FROM #test T
GROUP BY T.hierarchyval;
;with cte as
(
Select hierarchyid, hierarchyval,
row_number() over(Partition by hierarchyval order by hierarchyid) RowNum
from #test
)
select * from cte
where rownum = 1
;
/* Clustered Index */
ALTER TABLE #test ADD CONSTRAINT PK_TEST_BOTH_COLS PRIMARY KEY CLUSTERED (hierarchyval ASC,hierarchyid ASC);
SELECT
MIN(T.hierarchyid) AS [hierarchyid]
,T.hierarchyval
FROM #test T
GROUP BY T.hierarchyval;
;with cte as
(
Select hierarchyid, hierarchyval,
row_number() over(Partition by hierarchyval order by hierarchyid) RowNum
from #test
)
select * from cte
where rownum = 1
;
/* Unique nonclustered index */
CREATE UNIQUE NONCLUSTERED INDEX UNQNCLIDX_TMP_TEST_POC ON #test (hierarchyval ASC,hierarchyid ASC);
SELECT
MIN(T.hierarchyid) AS [hierarchyid]
,T.hierarchyval
FROM #test T
GROUP BY T.hierarchyval;
;with cte as
(
Select hierarchyid, hierarchyval,
row_number() over(Partition by hierarchyval order by hierarchyid) RowNum
from #test
)
select * from cte
where rownum = 1
;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply