Ranking/Partitioning data - 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)

Ranking/Partitioning data

Author

Topic

sauce1979
Starting Member

47 Posts

Posted - 2012-01-19 : 15:54:53

I have dataset that consists of 3 fields. Package number, IC_CODE and a description
I am trying to write a query that spilts/partitions data into groups based on the package number and the outputs data where for each package the data is the same.
I have provided some test data



CREATE TABLE #TEST
(
		PACKAGE INT
,		CODE VARCHAR(20)
,		[DESCRIPTION] VARCHAR(20)
)


INSERT INTO #TEST
SELECT 10000,'CSAC','CASING'UNION ALL
SELECT 10000,'BOXC','BOXING'UNION ALL
SELECT 10000,'CSAC','CASING' UNION ALL
SELECT 12000,'BOXC','BOXING'UNION ALL
SELECT 12000,'BOXC','BOXING'UNION ALL
SELECT 12000,'BOXC','BOXING' UNION ALL
SELECT 13000,'BASK','BASKET'UNION ALL
SELECT 13000,'CSAC','CASING'UNION ALL
SELECT 13000,'BOXC','BOXING'

Based on th above data the output should be

Package CODE DESCRIPTION
______________________________
12000 BOXC BOXING
12000 BOXC BOXING
12000 BOXC BOXING

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 16:02:59

Hello sauce1979,

Would'nt

10000 CSAC CASING

appear in the result set also as there is more than one record with matching values in your sample data set?

If so, then perhaps something like this would work:

SELECT t.* 
FROM #TEST t
JOIN 
(
	SELECT *
	FROM #TEST 
	GROUP BY PACKAGE, CODE, [DESCRIPTION]
	HAVING COUNT(*) > 1
) t1 ON t1.PACKAGE = t.PACKAGE AND t1.CODE = t.CODE AND t1.[DESCRIPTION] = t.[DESCRIPTION]

If not can you please elaborate on your criteria.

TIA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources