SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query

Author

Topic

skc40
Starting Member

34 Posts

Posted - 2013-12-22 : 17:58:16

Hi all, below is my table structure along with data-set.
DECLARE @Table TABLE (Emp_ID INT, Unique_ID INT, Dep_ID INT, RowID INT)
INSERT INTO @Table values (1,1000,100,1)
INSERT INTO @Table VALUES (1,1000,101,2)
INSERT INTO @Table VALUES (1,1000,102,3)
INSERT INTO @Table VALUES (1,1000,103,4)
INSERT INTO @Table values (1,2000,101,1)
INSERT INTO @Table values (1,2000,102,2)
INSERT INTO @Table VALUES (1,2000,103,3)
INSERT INTO @Table VALUES (1,3000,102,1)
INSERT INTO @Table VALUES (1,3000,103,2)
INSERT INTO @Table VALUES (1,4000,100,1)
INSERT INTO @Table VALUES (1,4000,101,2)
INSERT INTO @Table VALUES (2,5000,105,1)
INSERT INTO @Table VALUES (2,5000,105,2)
INSERT INTO @Table VALUES (2,6000,105,1)
INSERT INTO @Table VALUES (2,6000,106,2)
SELECT * FROM @Table

Below is the desired result-set.
Dep_ID Dep_ID Unique_ID
100 100 1000
100 101 1000
100 102 1000
100 103 1000
101 101 2000
101 102 2000
101 103 2000
102 102 3000
102 103 3000
105 105 5000
105 106 5000

Could you please help me solve this issue!!

Thanks in advance

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-12-23 : 00:27:32

;WITH CTE (Dep_Id,Unique_Id)
AS
(
SELECT DISTINCT T1.Dep_ID,T1.Unique_ID FROM @Table AS T1
Inner Join @Table AS T2
ON T1.Dep_ID IN(SELECT DISTINCT MIN(T3.Dep_Id) FROM @Table AS T3 Group By T3.Dep_Id)
WHERE T1.Unique_ID IN (1000,2000,3000,5000)
Group by T1.Dep_ID,T1.Unique_ID
)
SELECT DISTINCT C.Dep_Id,T.Dep_Id,MAX(C.Unique_Id)AS Unique_Id FROM CTE AS C
INNER JOIN @Table AS T
ON C.Unique_Id = T.Unique_ID
WHERE C.Dep_ID <=T.Dep_Id
GROUP BY T.Dep_Id,C.Dep_Id

veeranjaneyulu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-23 : 09:52:42

[code]
SELECT t1.Dep_ID,t2.Dep_ID,MIN(t1.Unique_ID) AS MinID
FROM @Table t1
INNER JOIN @Table t2
ON t2.Unique_ID = t1.Unique_ID
AND t2.Emp_ID = t1.Emp_ID
WHERE t1.RowID = 1
GROUP BY t1.Dep_ID,t2.Dep_ID
ORDER BY t1.Dep_ID,t2.Dep_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

skc40
Starting Member

34 Posts

Posted - 2013-12-23 : 13:04:54

Thank you all!!

Just what i wanted :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-23 : 13:25:15

Without self-join

-- SwePeso
WITH cteSource
AS (
	SELECT	MIN(Dep_ID) OVER (PARTITION BY Unique_ID, Emp_ID) AS a,
		Dep_ID AS b,
		Unique_ID,
		Emp_ID
	FROM	@Table
)
SELECT		a AS Dep_ID,
		b AS Dep_ID,
		MIN(Unique_ID) AS MinID
FROM		cteSource
GROUP BY	a,
		b
ORDER BY	a,
		b;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources