Results 1 to 7 of 7

Thread: How to compare previous row with current in SQL?

  1. #1
    Junior Member
    Join Date
    Jun 2009
    Answers
    5

    How to compare previous row with current in SQL?

    The following is the table content. I know that the COL2 values will always be increasing. I want to find out a row which DOES NOT increase compared to the previous row value. In this case the SQL result should be (666, 09) and (333, 08). Could you please guide or give the SQL that will do this? I greatly appreciate your help. Thank you.

    COL1 COL2
    999 11
    888 22
    777 33
    666 09
    555 44
    444 55
    333 08
    222 77


  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: How to compare previous row with current in SQL?

    What is the code that you are working on ?


  3. #3
    Junior Member
    Join Date
    Jun 2009
    Answers
    5

    Re: How to compare previous row with current in SQL?

    It is not any application. Just SQL will do. or PL SQL. It is a Oracle 9i db. Thank you.


  4. #4
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: How to compare previous row with current in SQL?

    I have asked for the code that you are working on currently .


  5. #5
    Contributing Member
    Join Date
    Sep 2006
    Answers
    42

    Re: How to compare previous row with current in SQL?

    One way I could think of doing it is this:
    1. Add another column which indicates the row precendence. Something similar to:
    Col1 Col2 Row Number
    999 11 1
    888 22 2
    777 33 3
    666 9 4
    555 44 5
    444 55 6
    333 8 7
    222 77 8

    2. Use this query
    Code:
    select 
    t1.col1, t1.col2
    from table t1, table t2
    where 
    t2.orderCol = (t1.orderCol - 1)
    and 
    t1.col2 < t2.col2
    Here, only those rows are printed where
    1. The Row Number of the table t2 is 1 less than row number from table t1
    (previous row from t2 is being selected)
    2. Value of col2 in t1 is less than that is t2
    (value of current col2 is less than previous col2)

    If data is less, this may be an easy way to do it. Won't recommend this for a large amount of data though. For a large amount of data, you could write a procedure using rank() or rownum().

    --Sahil.


  6. #6
    Junior Member
    Join Date
    Jun 2009
    Answers
    5

    Re: How to compare previous row with current in SQL?

    Thank you for help. It worked very well.


  7. #7
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: How to compare previous row with current in SQL?

    i think no need to modify existing struncture

    Select *
    From (Select ROWNUM r1, col1, col2
    From dummy1) t1,
    (Select ROWNUM r2, col1, col2
    From dummy1) t2
    Where t2.r2 = (t1.r1 - 1) And t1.col2 < t2.col2

    Sireesha

  8.  Sponsored Ads
    Ad


     

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact