How can I filter results in a SQL query?

Let’s say I have Products A-Z, and each product has a monthly observation for X Months, but for most products the value in the observation stays the same for that particular product, but I need to find the products that have observations with a new value, the moment the value in the observation changes. Also, you can have a double to quadruple entry per month if the value doesn’t only change, but coexists, and I need to find every instance that happens for a product.

Example in pic:

enter image description here

  • Which DBMS are you using? The lag function will help you if you have a DBMS that supports window/analytical functions. – Colin 't Hart Jun 1 '15 at 13:57
  • Can you expect only simple changes or double/triple-to-single-and-reverse? Or are their cases were there is both a "simple change" and a change from eg. double to single at the same time? – Colin 't Hart Jun 1 '15 at 13:59
  • Please provide us with sample data in the form of create table and insert script rather than just a picture. This will make it much easier for us to start helping you. – Colin 't Hart Jun 1 '15 at 14:00
>Product    Value    Date
>---------- -------- --------
>A          123      Jan-99
>A          123      Feb-99
>A          123      Mar-99
>A          123      Apr-99
>A          123      May-99
>A          456      Jun-99
>E          456      Jan-99
>E          456      Feb-99
>E          654      Apr-99
>E          987      Apr-99
>E          873      Apr-99
>E          987      May-99
>E          456      Jun-99
>Z          852      Jan-99
>Z          852      Feb-99
>Z          852      Mar-99
>Z          963      Apr-99
>Z          852      Apr-99
>Z          852      Jun-99

For this data set I would try and use a function like ROW_NUMBER() to partition the data into blocks that I would like to capture and then filter.

For changing values I would try something like

    SELECT * , 
           ROW_NUMBER( )OVER( PARTITION BY t.Product , 
                                           t.[Value] ORDER BY t.Product )AS  RowNumber
           FROM dbo.Table_1 t ) AS Result
  WHERE RowNumber = 1;

 Product    Value   Date    RowNumber
 A          123     Jan-99  1
 A          456     Jun-99  1
 E          456     Jan-99  1
 E          654     Apr-99  1
 E          873     Apr-99  1
 E          987     Apr-99  1
 Z          852     Apr-99  1
 Z          963     Apr-99  1

This should return unique values for each product, and then you should be able to quickly see where changes have occurred.

edit: LAG would be another great way to do this as mentioned by Colin.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.