Comaparing previous record with current in sql

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

Development Tools

ASP.NET

Comaparing previous record with current in sql

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-09-18 : 02:15:51

MenuID MenuName MainMenu CommandName URL
1 AR NULL
2 Order Processing AR
..
..
..
..
17 Service Processing AR
18 AP NULL
20 Purchase Processing AP ..
..
..
25 Payment Processing AP

I want output as
2 Order Processing AR
17 Service Processing AR
20 Purchase Processing AP
25 Payment Processing AP

How to do this ? How we can comapare previous record with current record ? ..
..

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2007-09-18 : 02:33:40

You may need to explain a little more about what you're trying to achieve. You can get your example output by simply using "WHERE MainMenu is not null) - but that's obviously not what you're after.

When you say "compare previous record with current record" do you mean compare with the immediately previous record or do you mean some record which occurs before and has some special value...?

Also, it might be helpful for you to post some example data like this

declare @test table (MenuID int, MenuName varchar(50), MainMenu varchar(50), CommandName varchar(50), URL varchar(50))
insert into @test (MenuID, MenuName, MainMenu) select 1, 'AR', null
insert into @test (MenuID, MenuName, MainMenu, CommandName) select 2, 'Order', 'Processing', 'AR'
insert into @test (MenuID, MenuName, MainMenu, CommandName) select 17, 'Service', 'Processing', 'AR'
insert into @test (MenuID, MenuName, MainMenu) select 17, 'AP', null
insert into @test (MenuID, MenuName, MainMenu, CommandName) select 17, 'Purchase', 'Processing', 'AP'
insert into @test (MenuID, MenuName, MainMenu, CommandName) select 25, 'Payment', 'Processing', 'AP'

so that people can quickly work out a solution for you and test it.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 03:27:56

[code]declare @test table (MenuID int, MenuName varchar(50), MainMenu varchar(50), CommandName varchar(50), URL varchar(50))
insert into @test (MenuID, MenuName, MainMenu) select 1, 'AR', null
insert into @test (MenuID, MenuName, MainMenu, CommandName) select 2, 'Order', 'Processing', 'AR'
insert into @test (MenuID, MenuName, MainMenu, CommandName) select 17, 'Service', 'Processing', 'AR'
insert into @test (MenuID, MenuName, MainMenu) select 18, 'AP', null
insert into @test (MenuID, MenuName, MainMenu, CommandName) select 20, 'Purchase', 'Processing', 'AP'
insert into @test (MenuID, MenuName, MainMenu, CommandName) select 25, 'Payment', 'Processing', 'AP'

select MenuID,
MenuName,
MainMenu,
CommandName,
URL
from @test
where commandname is not null

union all

select t2.MenuID,
t1.MenuName,
t1.MainMenu,
t1.CommandName,
t1.URL
from (
select t1.menuid,
(select min(menuid) from @test as t2 where t2.menuid > t1.menuid) as mnid
from @test as t1
where t1.commandname is null
) AS t2
inner join @test as t1 on t1.menuid = t2.mnid

order by menuid[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources