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

Comparing current and previous row values

My SupplyCosts table contains a list of supplies and the cost associated with that supply for every month. I want to select only the months where the cost has changed and the previous cost. SELECT DISTINCT doesn't work because I need the month field and it returns every record.

My SupplyCosts table contains a list of supplies and the cost associated with that supply for every month:

SupplyCosts: 
mth   item    cost
10-04 bandaid .20
11-04 bandaid .25
12-04 bandaid .30
01-05 bandaid .30

Some months the cost remains the same and the only change is the month field. Other months, the cost changes. I want to select only the months where the cost has changed and the previous cost. From the example data above, I only want the records for the 11-04 and 12-04 months. SELECT DISTINCT doesn't work because I need the month field and it returns every record. Thanks so much.

The trick with this type of problem, where you need to compare a value on one row with a value on another row, is to use a self-join. This particular scenario is complicated by the fact that you need a pretty hairy join clause, to match the current month with the previous month.

To see why, let's ask ourselves how we should compare 12-04 and 01-05. We can not simply say:

where t1.mth = t2.mth - 1

The reason we can't is obvious: 12-04 and 01-05 aren't numbers. They aren't dates, either. They're strings.

However, all is not lost. We can convert strings to dates easily enough, although the actual mechanism is different in just about every database system. Here's the solution using MySQL syntax:

select curr.item
     , curr.mth  
     , curr.cost as current_cost
     , prev.cost as previous_cost
  from SupplyCosts as prev
inner
  join SupplyCosts as curr
    on prev.item = curr.item
   and date_add(
         cast( 
           concat('20',substr(prev.mth,4,2)
                  ,'-',substr(prev.mth,1,2)
                  ,'-01') as date)
          , interval 1 month)
       = cast( 
           concat('20',substr(curr.mth,4,2)
                  ,'-',substr(curr.mth,1,2)
                  ,'-01') as date)        
 where prev.cost 
    <> curr.cost 
order 
    by curr.item
     , curr.mth 

The expressions involving DATE_ADD and CAST will change from one database system to another. Basically you convert the strings to dates (using the first of the month), and then use date arithmetic (in this case the DATE_ADD function) to add 1 month.

This was last published in August 2005

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