1

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?

jpw
  • 44,361
  • 6
  • 66
  • 86
mike_x_
  • 1,900
  • 3
  • 35
  • 69

3 Answers3

2

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

Community
  • 1
  • 1
Sunil HV
  • 51
  • 3
  • How can i do this for db2? (i ve updated my question) Thanks! – mike_x_ Nov 11 '14 at 09:13
  • This should work for db2, they have a lag function that you can use : http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0023461.html?cp=SSEPGG_9.7.0%2F2-10-2-10-9 – Sunil HV Nov 11 '14 at 09:16
  • The MySQL hack for this mentioned in this answer works great! – Grant Mar 22 '17 at 09:12
2

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
jpw
  • 44,361
  • 6
  • 66
  • 86
1

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
 ;
selmand
  • 51
  • 3