Filtering SQL Search Query 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:
&gt;Product    Value    Date
&gt;---------- -------- --------
&gt;A          123      Jan-99
&gt;A          123      Feb-99
&gt;A          123      Mar-99
&gt;A          123      Apr-99
&gt;A          123      May-99
&gt;A          456      Jun-99
&gt;E          456      Jan-99
&gt;E          456      Feb-99
&gt;E          654      Apr-99
&gt;E          987      Apr-99
&gt;E          873      Apr-99
&gt;E          987      May-99
&gt;E          456      Jun-99
&gt;Z          852      Jan-99
&gt;Z          852      Feb-99
&gt;Z          852      Mar-99
&gt;Z          963      Apr-99
&gt;Z          852      Apr-99
&gt;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 *
  FROM( 
    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.