Replicate the top row value - 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 2005 Forums

Transact-SQL (2005)

Replicate the top row value

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-04 : 06:45:27

Hi, I'm not to sure if this is possible.

I want to replicate the top row value.
Below in the example the second row Description field's value is ''
How can i replicate the top row value where there is blank within the same process.

Process Description
1 Test
1

I have the same problem where there is a different value, the db data is a bit messy.

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-04 : 06:48:17

See http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-04 : 07:40:34

start with this

DECLARE @T TABLE (ID INT, VAL VARCHAR(10))

INSERT INTO @T
			SELECT 1, 'A'
UNION ALL	SELECT 1, ''
UNION ALL	SELECT 2, 'B'
UNION ALL	SELECT 2, ''

SELECT	*,
		CASE WHEN VAL = '' THEN ( SELECT MAX(VAL) FROM @T WHERE ID = T.ID) ELSE VAL END AS 'NewVAL'
FROM	@T T

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-04 : 07:49:57

Then I guess this will work to (but not so efficient)

SELECT	ID,
	VAL,
	MAX(VAL) OVER (PARTITION BY ID) AS NewVal
FROM	@T

E 12°55'05.63"
N 56°04'39.26"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-04 : 08:56:27

Thank you all and apologies for the question - Under pressure
It's not really efficient,I done the following:

from dbo.fcEventHist EHistTask

join (select Event.processid as EventProcess, Event.CreateDate as EventCreateDate, Event.CreateId as EventCreateID from dbo.fcEventHist Event
join (select max(EHist.id) as MaxEvId, processid as MaxEvProcess from dbo.fcEventHist EHist where EHist.EventID in ('95','123') group by processid) as MaxEv
on MaxEv.MaxEvId = Event.ID and MaxEv.MaxEvProcess = Event.processid) as MaxEvent
on EHistTask.ProcessID = MaxEvent.EventProcess

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-04 : 09:25:45

Oh, you want last record of each group?
Try the new ROW_NUMBER() function.

SELECT	*
FROM	(
		SELECT	*,
			ROW_NUMBER() OVER (PARTITION BY ProcessID ORDER BY ID DESC) AS RecID
		FROM	dbo.fcEventHist
	) AS d
WHERE	RecID = 1

E 12°55'05.63"
N 56°04'39.26"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-12-05 : 03:57:34

Thank You

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources