How to compare the previous row with current row in SQL Server 2000

Hi,
I have to compare the previous row with current row in a table in SQL Server 2000. Please help me how to do this in a optimized way. Table contains nearly 30 columns and rows count is more than 10 digits.Rows in a result set have no ipmlicit order, so you have to define what "previous" means for this to make any sense. If you mean the previous row in an arbitrary result set (like SELECT * FROM mytable), then your question is meaningless in a SQL context.

-PatP|||My problem is by taking any result set order by like (SELECT * from my table ORDER By Primary key column)
and then Comparing 1st row with 2nd
2nd row with 3rd
3rd row with 4th and so on...|||CREATE TABLE #patp (
patpId INT NOT NULL
CONSTRAINT XPKpatp PRIMARY KEY (patpId)
, baggage VARCHAR(20) NOT NULL
)

INSERT INTO #patp (patpId, baggage)
SELECT 1, 'One' UNION
SELECT 2, 'Two' UNION
SELECT 3, 'Three' UNION
SELECT 5, 'Five' UNION
SELECT 7, 'Seven' UNION
SELECT 11, 'Eleven' UNION
SELECT 13, 'Thirteen' UNION
SELECT 17, 'Seventeen' UNION
SELECT 19, 'Nineteen' UNION
SELECT 23, 'Twenty-Three'

SELECT *
FROM #patp AS a
JOIN #patp AS b
ON (b.patpId = (SELECT Max(z.patpId)
FROM #patp AS z
WHERE z.patpId < a.patpId))-PatP|||Thank you for your response.

Exactly my problem is:

Main Table

AccountNo Version Status Qunatity LastVersion
1 8 I 0 1

History Table

AccountNo Version Status Qunatity LastVersion
1 2 I 0 0
1 3 I 0 0
1 4 I 1 0
1 5 A 1 0
1 6 A 1 0
1 7 I 0 0
1 8 I 0 1

Child Table

AccountNo DocumentID Version Flag LastVersion
1 10 2 0 0
1 11 2 1 0
1 10 3 0 1
1 11 3 1 1

Here I have to filter the duplicate records in History table and child table

Scenario is:

I have to compare first row(2 nd version) with 2 nd row(3 rd version),
if status = I and all columns matches excluding version and LastVersion
then compare in child table for same accountNo for same version,
i.e., 2 version rows with 3 rd version rows for same account no
and documentID, if matches then delete 2nd version row in history and
child tables, like that I have to compare each row. This is only for status I.
finally I have to rearrange all versions and main table will get updated with
the row with higher version in the history table for same accountNo.

Finally result is:

Main Table

AccountNo Version Status Qunatity LastVersion
1 6 I 0 1

History Table (deleted 3 and 8 the version in the original history table,
rearranged versions and last version as 1 for last record

AccountNo Version Status Qunatity LastVersion
1 2 I 0 0
1 3 I 1 0
1 4 A 1 0
1 5 A 1 0
1 6 I 0 1

Child Table

AccountNo DocumentID Version Flag Lastversion
1 10 2 0 1
1 11 2 1 1

Problem: I used cursors to fetch records and compare one by one.
that table is having huge data. Only for retrieving all records it will take
30 min. So totally it is taking nearly 2 hrs. It is having morethan 30 columns.
So please could you tell me how to optimize this.|||Hello every one,

Please help me regarding this issue.
I used cursors to delete the duplicate records.
Nearly for 1000 records it is taking more than 1 hr.
I have record count more than 10 digit.
How to optimize this??|||Yeah, cursors are to be avoided in SQL server, as you are finding out. Use set-based logic to remove your duplicates instead.
Give us the DDL creation script for your table, and the fields you want to comprise a unique key.|||Did you read the hint link at the top of the forum?

Also the order of database has no meaning.

Do you have an IDENTITY Column or a datetime column for when the row was added?

Read the Hint sticky and post some info.