Q
Problem solve Get help with specific problems with your technologies, process and projects.

Unpredictable consecutive rows

I want to compare consecutive rows in a table, e.g., 1-2, 2-3, etc. But how do I do this, as none of the columns are predictable?

I want to compare consecutive rows in a table, e.g., 1-2, 2-3, etc. Now I was thinking of doing a self join for...

this. But the problem is, how do I do this, as none of the columns are predictable. For example, if I have the following data:

name quantity cost
 A      2      20
 H      7      24
 L      1      12
...

and I want to find out the difference between the cost of A and H, H and L, and so on.

Interesting question. We need to join each row to the row which has the lowest name of all the names that are greater than the name in the row being joined. Simple, yes? I mean, once you say it like that, it's easy to understand, right?

Well, once you say it like that, it's easy to write the SQL for, too.

select t1.name 
     , t1.cost as t1_cost
     , t2.cost as t2_cost
     , t2.cost - t1.cost as diff 
  from yourtable as t1
left outer
  join yourtable as t2
    on t2.name
     = ( select min(name)
           from yourtable
          where name > t1.name ) 
          
name t1_cost t2_cost diff
 A     20      24      4
 H     24      12    -12 
 L     12      --     -- 

It's an unusual ON condition, because you don't see one like this every day. It may be rare but it is perfectly valid.

Rephrasing a question may seem difficult, but, like everything else that's worth doing in this life, it gets easier the more often you do it. To be fair, though, perhaps one must know in advance how to rephrase the question the way I did, or even that this type of join was possible.

This is a nice example to remember. Thank you for the interesting question.

This was last published in January 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close