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.
| Author |
Topic |
|
ajaeger
Starting Member
4 Posts |
 Posted - 2002-10-12 : 16:30:35
|
| Thought I posted this the other day, but can't find it so I may not have...sorry if it is a duplicate.I'm trying to compare a field in one record to the same field in the previous record. For example:"if ORDER_NUMBER in this record is equal to ORDER_NUMBER in the previous record then..."I've been able to do this in Crystal Reports with the Previous({FIELD_NAME}) function, but can't seem to figure out how to do it in SQL.Thanks.Anna |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-10-13 : 22:57:13
|
| There's no natural ordering of rows in SQL, so you may need to add either a datetime column or a column with a sequence number if you want to locate your *previous* record.From there with a Sequence Column select @ONUM1 = ORDER_NUMBER FROM xxxxx WHERE Seqcol=@O1select @ONUM2 = ORDER_NUMBER FROM xxxxx where Seqcol=@O1-1IF @ONUM1 = @ONUM2 BEGIN .. whatever ENDSam |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-14 : 08:19:06
|
| select t1.srtfld, t2.srtfld, t1,fld, t2.fldfrom tbl.t1, tbl.2where t2.srtfld = (select min(t3.srtfld) from tbl t3 where t3.srtfld > t1.srtfld)and t1.fld <> t2.fldThis will give all the records where the field changes.Use a case statement if you just want to highlight changes.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ajaeger
Starting Member
4 Posts |
Posted - 2002-10-14 : 08:21:35
|
| Thanks for the solutions! ANNAAnna |
|
|
|
|
|
|
Compare current record to previous record