Cfrgtkky

I have a table that shows a products price on a daily basis. A product has 3 prices: wholesale, volume and clearance.

The table is updated everyday with the current prices, most days no changes occur. However, I want to select the rows where one of the values has changed and I'm struggling for ideas. I've tried a group by but because prices go up and down this won't work.

select date_entered, 

       WholeSale,

       Volume,

       Clearance 

FROM pricetable 

where product = 'TANGO'

order by date_entered desc

The above selects all the rows from the table for a set product.

How can I make it so only rows where a change to, wholesale or volume or clearance has occurred.

Any help appreciated.

select p1.date_entered, 

       p1.WholeSale,

       p1.Volume,

       p1.Clearance 

FROM pricetable p1

CROSS APPLY

--cross apply to most recent prior record

(SELECT TOP 1 * 

        FROM pricetable p2 

            where p1.product = p2.product 

                and p2.date_entered < p1.date_entered 

                    order by p2.date_entered desc) CA

where p1.product = 'TANGO'

  and (p1.wholesale != CA.wholesale or p1.volume != CA.volume or p1.clearence != CA.clearence)

order by p1.date_entered desc

THE QUESTION WAS ORIGINALLY TAGGED SQL-SERVER (with no version). This works for SQL Server 2012+:

Use lag(). Something like this:

select pt.*

from (select pt.*,

             lag(wholesale) over (partition by product 

                                  order by date_entered) as prev_wholesale,

             lag(volume)    over (partition by product 

                                  order by date_entered) as prev_volume,

             lag(clearance) over (partition by clearance 

                                  order by date_entered) as prev_clearance

      from pricetable

      where product = 'TANGO'

     ) pt

where (prev_wholesale is null or prev_wholesale <> wholesale) or

      (prev_volume is null or prev_volume <> volume) or

      (prev_clearance is null or prev_clearance <> clearance)          

order by date_entered desc;

Compare every row's values to the previous day's values:

SELECT * FROM pricetable p WHERE (p.product = 'TANGO') AND

(

    (p.WholeSale <> (SELECT WholeSale FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))

OR

    (p.Volume <> (SELECT Volume FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))

OR

    (p.Clearance <> (SELECT Clearance FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))

)

You might be able to use ROW_NUMBER() to determine previous rows:

WITH cte AS (

    SELECT product, date_entered, wholesale, volume, clearance, ROW_NUMBER() OVER (PARTITION BY product ORDER BY date_entered) AS rn

    FROM pricetable

)

SELECT main.*

FROM cte AS main

LEFT JOIN cte AS prev ON prev.product = main.product AND prev.rn = main.rn - 1

WHERE main.rn = 1

OR prev.wholesale <> main.wholesale

OR prev.volume    <> main.volume

OR prev.clearance <> main.clearance