Problem, Successively repeating rows !!

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 2005 Forums

Transact-SQL (2005)

Problem, Successively repeating rows !!

Author

Topic

luky_13
Starting Member

2 Posts

Posted - 2007-12-05 : 01:24:14

I have this table and data in SQL 2005, and require the result as listed below. Any advice / help would be appreciated a lot.

CREATE TABLE [dbo].[PMC3](
[productCode] [nchar](7) NOT NULL,
[serialNo] [smallint] NOT NULL,
[processMaster] [int] NOT NULL,
[process] [bigint] NOT NULL,
[specs] [nvarchar](50) NOT NULL,
[remarks] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_PMC3] PRIMARY KEY CLUSTERED
(
[productCode] ASC,
[serialNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 1, 1, 1001, 'Step1, Sub step 1, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 2, 1, 1008, 'Step1, Sub step 2, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 3, 1, 1001, 'Step1, Sub step 3, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 4, 1, 1008, 'Step1, Sub step 4, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 5, 1, 1001, 'Step1, Sub step 5, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 6, 1, 1008, 'Step1, Sub step 6, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 7, 3, 1011, 'Step2, Sub step 1, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 8, 2, 1009, 'Step3, Sub step 1, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 9, 4, 1014, 'Step4, Sub step 1, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 10, 2, 1010, 'Step5, Sub step 1, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 11, 5, 1012, 'Step6, Sub step 1, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 12, 3, 1011, 'Step7, Sub step 1, 'None')
INSERT PMC3 (productCode, serialNo, processMaster, process, specs, remarks) values ('TVS0003', 13, 5, 1013, 'Step8, Sub step 1, 'None')

The result must be
productCode, rowNumber, processMaster
TVS0003, 1, 1
TVS0003, 2, 3
TVS0003, 3, 2
TVS0003, 4, 4
TVS0003, 5, 2
TVS0003, 6, 5
TVS0003, 7, 3
TVS0003, 8, 5

Need to eliminate successive repeating rows, example the 1st row inserted and the second have the same processMaster = 1, want to eliminate such rows. Only when the processMaster changes to another number it should be included in the result set. In other words i want all the Steps, but not the steps repeating in succession.

Thanks
Mandeep

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-05 : 02:48:36

What do you mean by "eliminate successive repeating rows" because when you insert the data in the table the table contains records like this.

TVS0003 1 1 1001 Step1, Sub step 1 None
TVS0003 2 1 1008 Step1, Sub step 2 None
TVS0003 3 1 1001 Step1, Sub step 3 None
TVS0003 4 1 1008 Step1, Sub step 4 None
TVS0003 5 1 1001 Step1, Sub step 5 None

But you said you want
TVS0003, 1, 1
TVS0003, 2, 3
TVS0003, 3, 2
TVS0003, 4, 4
TVS0003, 5, 2
TVS0003, 6, 5
TVS0003, 7, 3
TVS0003, 8, 5

means you dont want to delete the records completely but just want to update the processmaster for every repeating processmaster?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 03:49:46

[code]DECLARE @PMC3 TABLE (ProductCode NCHAR(7), SerialNo SMALLINT, ProcessMaster INT, Process BIGINT, Specs NVARCHAR(50), Remarks NVARCHAR(200))

INSERT @PMC3
SELECT 'TVS0003', 2, 1, 1008, 'Step1, Sub step 2', 'None' UNION ALL
SELECT 'TVS0003', 3, 1, 1001, 'Step1, Sub step 3', 'None' UNION ALL
SELECT 'TVS0003', 4, 1, 1008, 'Step1, Sub step 4', 'None' UNION ALL
SELECT 'TVS0003', 5, 1, 1001, 'Step1, Sub step 5', 'None' UNION ALL
SELECT 'TVS0003', 6, 1, 1008, 'Step1, Sub step 6', 'None' UNION ALL
SELECT 'TVS0003', 7, 3, 1011, 'Step2, Sub step 1', 'None' UNION ALL
SELECT 'TVS0003', 8, 2, 1009, 'Step3, Sub step 1', 'None' UNION ALL
SELECT 'TVS0003', 9, 4, 1014, 'Step4, Sub step 1', 'None' UNION ALL
SELECT 'TVS0003', 10, 2, 1010, 'Step5, Sub step 1', 'None' UNION ALL
SELECT 'TVS0003', 11, 5, 1012, 'Step6, Sub step 1', 'None' UNION ALL
SELECT 'TVS0003', 12, 3, 1011, 'Step7, Sub step 1', 'None' UNION ALL
SELECT 'TVS0003', 13, 5, 1013, 'Step8, Sub step 1', 'None'

SELECT ProductCode,
ROW_NUMBER() OVER (ORDER BY SerialNo) AS RowNumber,
ProcessMaster
FROM (
SELECT s.ProductCode,
s.SerialNo,
s.ProcessMaster,
(SELECT TOP 1 t.ProcessMaster FROM @PMC3 AS t WHERE t.SerialNo < s.SerialNo ORDER BY t.SerialNo DESC) AS pm
FROM @PMC3 AS s
) AS q
WHERE ProcessMaster <> pm
OR pm IS NULL
ORDER BY SerialNo[/code]

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-05 : 13:45:40

I assume, Peso's query is really waht you are lookign for. But given the sample data, could you use the Specs column to get what you need?

SELECT 
		ProductCode,
		ROW_NUMBER() OVER (ORDER BY SerialNo) AS RowNumber,
		ProcessMaster
FROM 
	@PMC3 AS PM
WHERE 
	Specs LIKE '%Sub step 1'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 13:48:06

Brilliant!

E 12°55'05.25"
N 56°04'39.16"

luky_13
Starting Member

2 Posts

Posted - 2007-12-07 : 00:35:02

Amazing.... lot of talent around on this site !! Thanks guys .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources