How to get column value from previous row for each group?

Please read the question, is not a duplicate.

I'll try to explain the problem right:

I have a table like this:

Table: stg_mov_salida

id_movement | id_product | id_warehouse | movement_date | id_sale | sale_value | ...
209338          54545          2          "2011-05-21"    163299     75.95
209306          54545          2          "2011-06-05"    163267     75.95
209347          54545          2          "2012-10-26"    163308     77.95
209399          54545          3          "2011-01-22"    163360     79.95
209302          54545          3          "2011-01-23"    163263     72.95
209304          54545          3          "2011-01-24"    163265     72.95

And I want to get the sale_value of the previous row, but only if the previous row belongs to the same id_product and id_warehouse that the current row.

The table stg_mov_salida is a staging table ordered by:

 id_product asc, id_warehouse asc, movement_date asc

As I've read on other questions (like this or similar), I've tried to do it without success. The last query (and the closest to my solution) I've tried is this:

SELECT x2.*,
   (SELECT x2.sale_value AS last_sale_value
            FROM stg_mov_salida ms 
                WHERE x2.id_product = ms.id_product 
                AND x2.id_warehouse = ms.id_warehouse
                AND x2.date_movement  > ms.date_movement 
            ORDER BY ms.id_product asc, 
                             ms.id_warehouse asc, 
                             ms.date_movement asc
            LIMIT 1)
FROM stg_mov_salida x2

But this query is so slow that I've never seen it finishing, even for only one given id_product, therefore if this query is correct, I can't use it because is too slow.

Anyone knows how to solve my problem?

UPDATE:

Expected result:

id_movement | id_product | id_ware | mov_date | id_sale | sale_value | prev_sale_value
  209338       54545          2    "2011-05-21"  163299     75.95           null
  209306       54545          2    "2011-06-05"  163267     75.95           75.95
  209347       54545          2    "2012-10-26"  163308     77.95           75.95
  209399       54545          3    "2011-01-22"  163360     79.95           null
  209302       54545          3    "2011-01-23"  163263     72.95           79.95
  209304       54545          3    "2011-01-24"  163265     72.95           72.95

The null values on prev_sale_value would be because that row is the first on the group of (id_product, id_warehouse), but the idea is to replace that null with the sale_value of the same row directly, but I'll do it when I get the correct prev_sale_value for each row.

Any help would be appreciated

For easily response, here is the SQLFiddle

I've tried this query too, but it doesn't works properly:

select x2.*,
lag(sale_value, 1) over (partition by sale_value
                                order by id_product asc,
                                id_warehouse asc,
                                date_movement asc) as last_sale_value

from stg_mov_salida x2
order by id_product asc, id_warehouse asc, date_movement asc

Answers


Using lag is the right way to go, but your arguments are wrong. As you stated:

I want to get the sale_value of the previous row, but only if the previous row belongs to the same id_product and id_warehouse that the current row.

This means that your partiton by clause should include id_product and id_warehouse:

select x2.*,
lag(sale_value, 1) over (partition by id_product asc, id_warehouse asc
                         order by date_movement asc) as last_sale_value    
from stg_mov_salida x2
order by id_product asc, id_warehouse asc, date_movement asc

Need Your Help

How does one target IE7 and IE8 with valid CSS?

css internet-explorer internet-explorer-8 web-standards

I want to target IE7 and IE8 with W3C-compliant CSS. Sometimes fixing CSS for one version does not fix for the other. How can I achieve this?