CODEWITHSUNDEEP
sql
user3681654
user3681654

Reputation: 41

last_value partition

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

Upvotes: 2

Views: 74

Answers (1)

sqluser
sqluser

Reputation: 5672

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

Upvotes: 1

Related Questions