Validate the data from last row to first row

[techipa]

Hello All,

I have a following table:
Name New Version
P001 New P001 V1
P001 V1 P001 V2
P001 V2 P001 V3
P001 V3 P001 V4
P001 V4
B001 New

If the new name is added to the table then I should be able to add the new Name (B001 New)
If the new version to the name is added then update the existing version to the new version (P001 V3 -> P001 V4)

For update the conditions would be the New Version col is NULL and the content of Name col does not exists in the system. And it should match to the previous row's New version col. Then again the Name of that row shoudl match to the previous New Version.
I should be able to check this till it reaches the first row.

Can anyone tell me how check the value from latest record to the first record.

I would appreciate any suggestions.

Thanks in advance,
-techiPA

 

[AlexCuse]

Is that really all you have in the table, or is this a 'complexity hidden' type of deal?

You need a way to physically order your rows in order to accomplish this. It LOOKS like you can order by name, but I am not sure of that. If you want to just check for A previous version (not necessarily the previous row) try this:

[COLOR=blue]select[/color] a.Name, b.NewVersion [COLOR=blue]as[/color] OldName
[COLOR=blue]from[/color] 
[COLOR=green]--limit left table to only new records
[/color](
[COLOR=blue]select[/color] * [COLOR=blue]from[/color] myTable [COLOR=blue]where[/color] NewVersion [COLOR=blue]is[/color] Null
) a
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] myTable b
[COLOR=blue]on[/color] a.Name = b.NewVersion

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[techipa]

Thanks for the reply Alex.

 

[AlexCuse]

techipa-

I could have sworn there was more to your reply than that. Anyways, I wrote a function that will get you the first version. This will be no speed demon, but I think it will work for you. Check this out:

[COLOR=blue]create[/color] [COLOR=#FF00FF]function[/color] firstVersion(@vName [COLOR=blue]varchar[/color](20)) 

returns [COLOR=blue]varchar[/color](20)

[COLOR=blue]as[/color]

[COLOR=blue]begin[/color]

[COLOR=blue]declare[/color] @priorV [COLOR=blue]varchar[/color](20)

[COLOR=blue]select[/color] @priorV = [COLOR=blue]name[/color] [COLOR=blue]from[/color] versions [COLOR=blue]where[/color] NewVersion = @vName

[COLOR=blue]if[/color] @priorV [COLOR=blue]is[/color] not null
[COLOR=blue]begin[/color]
	[COLOR=blue]while[/color] exists([COLOR=blue]select[/color] * [COLOR=blue]from[/color] versions [COLOR=blue]where[/color] NewVersion = @priorV)
	[COLOR=blue]begin[/color]
	[COLOR=blue]select[/color] @priorV = [COLOR=blue]name[/color] [COLOR=blue]from[/color] versions [COLOR=blue]where[/color] NewVersion = @priorV
	[COLOR=blue]end[/color]
[COLOR=blue]end[/color]
[COLOR=blue]else[/color]
[COLOR=blue]begin[/color]
	[COLOR=blue]set[/color] @priorV = @vName
[COLOR=blue]end[/color]


[COLOR=blue]return[/color] @priorV

[COLOR=blue]end[/color]

You can call it like this:

[COLOR=blue]select[/color] [COLOR=blue]Name[/color], dbo.firstVersion([COLOR=blue]Name[/color]) [COLOR=blue]from[/color] Versions

This will not handle for gaps, and I don't know your data well enough to decide how to identify them, but it goes back as far as it can, and I think that it will give you what you need to identify the gaps.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom