Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2005
    Posts
    6

    Unanswered: 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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Nov 2005
    Posts
    6
    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...

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    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

  5. #5
    Join Date
    Nov 2005
    Posts
    6
    HTML Code:
    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.

  6. #6
    Join Date
    Nov 2005
    Posts
    6
    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??

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •