last_value partition

Question

Select speed, ram, price, 
       last_value(price) over (partition by speed, ram order by speed, ram) as lastp 
from PC

PC table

code    model   speed   ram  hd     cd  price

 1      1232    500     64   5.0    12x 600.0000
 10     1260    500     32   10.0   12x 350.0000
 11     1233    900     128  40.0   40x 980.0000
 12     1233    800     128  20.0   50x 970.0000
 2      1121    750     128  14.0   40x 850.0000
 3      1233    500     64   5.0    12x 600.0000
 4      1121    600     128  14.0   40x 850.0000
 5      1121    600     128  8.0    40x 850.0000
 6      1233    750     128  20.0   50x 950.0000
 7      1232    500     32   10.0   12x 400.0000
 8      1232    450     64   8.0    24x 350.0000
 9      1232    450     32   10.0   24x 350.0000

speed   ram price       lastp

450     32  350.0000    350.0000
450     64  350.0000    350.0000
500     32  350.0000    350.0000
500     32  400.0000    350.0000

Can anyone explain why in speed 500 ram 32 lastp is 350 not 400

Because in the window you have, your last value for price is 350. Try to run `ROW_NUMBER` over it to see which one is the last row you get per partition — sqluser, May 07 '15 at 03:34
interesting..i did that and it appears the last value for price is indead 350 not 400, how come? I thought it was already grouped so the last value for price is 400 — user3681654, May 07 '15 at 03:45
i dont think thats the case how can 350 be last row and 400 be first...makes no sense since its group by speed and then ram — user3681654, May 07 '15 at 03:53
It would be useful if you can provide some sample data before windowing your result set. — sqluser, May 07 '15 at 03:55
`LAST_VALUE` can return unexpected results if you are not careful: http://stackoverflow.com/questions/15388892/sql-last-value-returns-wrong-result-but-first-value-works-fine/26806428#26806428 http://stackoverflow.com/questions/29971151/last-value-in-sql-server-2012-is-returning-weird-results — Vladimir Baranov, May 07 '15 at 05:09

score 1 · Answer 1 · answered May 07 '15 at 04:42

You can make another query based on the main one. I don't know your dbms, but you can try this in most databases.

;WITH C AS(
Select speed, ram, price,
        ROW_NUMBER() over (partition by speed, ram order by speed, ram) as Rn
from tbl
)
SELECT speed, ram, price
  ,LAST_VALUE(price) over (partition by speed, ram order by speed , ram) as lastp
  FROM C
ORDER BY speed, ram, Rn DESC

SQLFiddle for SQL Server

last_value partition

1 Answers1