Previous Row

[KirillToronto]

I have a table with 2 columns called Department and Order Num,

Order Department
1 Dept01
1 Dept02
1 Dept03
1 Dept04
1 Dept05

I need to have a new table with prev dept in the thrord column like
Order Department Prev.Dept
1 Dept01 START
1 Dept02 Dept01
1 Dept03 Dept02
1 Dept04 Dept03
1 Dept05 Dept04

I am using SQL2000. Any help please

 

[bborissov]

And how do you know which record is PREVIUOS?
What define a record as such?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[KirillToronto]

My appology,
I have a seq column that seem to increment as new step is added.

Order Department Seq
1 Dept01 100
1 Dept02 150
1 Dept02 150
1 Dept03 200
1 Dept02 250
1 Dept04 300
1 Dept05 350
1 Dept06 400

 

[RyanEK]

Try

select order, department,
isnull((select top 1 department from [table] t2 where t2.seq < t1.seq
order by t2.seq desc),'START') as PrevDept
from [table] t1

 

[KirillToronto]

This will not work because "TOP 1" will return the same Dept regardless of the seq.(i.e. Seq of 500 or seq of 400 will yeild the same result)

Thanks, Kirill

 

[NastiaMurzin]

The simplest thing is to create the table with the desired features. Here is the script.

use tableschema;
go
create procedure Createtable2
as
create table Order2
(Order int as primary key not null,
Department varchar(50) null,
Seq int null);
go
insert into Order2 values (1, 'Dept01', 100);
insert into Order2 values (1, 'Dept02', 150);
insert into Order2 values (1, 'Dept02', 150);
insert into Order2 values (1, 'Dept03', 200);
insert into Order2 values (1, 'Dept02', 250);
insert into Order2 values (1, 'Dept04', 300);
insert into Order2 values (1, 'Dept05', 350);
insert into Order2 values (1, 'Dept06', 400);
go
select * from Order;
go

 

[RyanEK]

Not sure what you mean. Did you run my query?

 

[NastiaMurzin]

I was following the second requirement.

 

[NastiaMurzin]

One thing I would recommend is dropping the table before creating it. Here is the script.

use tableschema;
go
create procedure Droptable1
as
if table Order2 is not null
drop table Order2;
go
create procedure Createtable2
as
create table Order2
(Order int as primary key not null,
Department varchar(50) null,
Seq int null);
go
insert into Order2 values (1, 'Dept01', 100);
go
insert into Order2 values (1, 'Dept02', 150);
go
insert into Order2 values (1, 'Dept02', 150);
go
insert into Order2 values (1, 'Dept03', 200);
go
insert into Order2 values (1, 'Dept02', 250);
go
insert into Order2 values (1, 'Dept04', 300);
go
insert into Order2 values (1, 'Dept05', 350);
go
insert into Order2 values (1, 'Dept06', 400);
go
select * from Order;
go

 

[RyanEK]

I was referring to KirillToronto. I don't believe his problem involves the creation of the actual table.

 

[markros]

Too bad it's SQL Server 2000. It would be a piece of cake in SQL Server 2005 and up.

PluralSight Learning Library

 

[KirillToronto]

It is an off site db with SELECT only permissions. I cannot create/drop tables there.

RyanEK. when selecting top 1, I will always get 1st previous records which means when Seq is 400 or 300 or any other value, top 1 will always return lowest Seq which is 1, which means that Previous row for seq 400 is exactly the same as one for 500 or 200.


Thanks, Kirill

 

[RyanEK]

Hmm so how are these results wrong?

declare @tmp table (ord int, dept varchar(10), seq int)
insert into @tmp values (1, 'Dept1', 100);
insert into @tmp values (1, 'Dept2', 150);
insert into @tmp values (1, 'Dept2', 150);
insert into @tmp values (1, 'Dept3', 200);
insert into @tmp values (1, 'Dept2', 250);
insert into @tmp values (1, 'Dept4', 300);
insert into @tmp values (1, 'Dept5', 350);
insert into @tmp values (1, 'Dept6', 400);

select ord, dept, 
isnull((select top 1 dept from @tmp t2 where t2.seq < t1.seq
order by t2.seq desc),'START') as PrevDept
from @tmp t1

From the @tmp table above, what is your expected output?

Ryan

 

[NastiaMurzin]

It is very simple. You cannot choose from what you don't have. I will explain. You have only select privileges. You do not have a column Seq. You cannot select it, and you cannot insert values into column that does not exist. The only way to select from Seq is by creating column Seq first. Who does have the privilege to create a third column?