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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading