Get previous record column value in SQL

Question

I have a table that has 3 columns: date,name,salary

I want to query the table and add in the result set another calculated column that can have 2 values: 'new' and 'same'.

The rule to assign the value is: order records by date ascending. if the name of the current record is different from the name of the previous record then the new column value will be 'start' else (if it is the same) it will be 'same'.

How can i do that? is there a function (something like prevRow())? Or do i have to make a "trick"?

How can i do this in mysql and DB2?

Answer 1

It would seem that DB2 (versions after 9.7 at least) support the LAG() window function, so this query should work (although I haven't been able to test it):

SELECT 
    date,
    name,
    salary
    CASE
      WHEN lag(name, 1) OVER (ORDER BY date ASC) = name THEN 'same' 
      ELSE 'start' 
    END AS calc_col
FROM your_table
ORDER BY date ASC

Answer 2

You can use some query like this

set @last_val = "";
select 
    if(@last_val = column,"same","start") as state,
    @last_val := colum as temp
 from table
 ;

Answer 3

Depends on the database you are using, MS SQL has a lag function : http://msdn.microsoft.com/en-us/library/hh231256.aspx

There is a mysql hack for this, check out this question : Simulate lag function in MySQL how to do lag operation in mysql