Can anyone help me to do this :

Select previous field based on previous field in sql. I had tried a lot of ways but it seems doesn't works.

Select alert_value , year_id
from company_alert
where year_id = "2010" and year_id = year_id - 1
group by year_id

could anyone help me on how to do this :(

IF am right, are you trying to get all records where year_id in the range of 2010 and below?

SELECT alert_value , year_id
from company_alert
WHERE year_id <= '2010'
GROUP BY year_id

Edited 5 Years Ago by Netcode: n/a

I have been detect the previous year. but I face another problems which is when I compare the year, the stock_code is not the same. I need to have the previous year with the same stock code..please help me

SELECT cur.stock_code, cur.alert_id, cur.year_id,cur.period_id, cur.alert_value, prev.stock_code AS stock, prev.alert_id AS alert, prev.year_id AS prev_year_id,prev.period_id AS prev_period_id, prev.alert_value AS prev_alert_value
FROM company_alert AS cur
LEFT OUTER JOIN company_alert AS PREV ON cur.year_id = prev.year_id + 1 
WHERE cur.alert_id = prev.alert_id AND cur.year_id BETWEEN 2008 AND 2011
ORDER BY year_id

I have been detect the previous year. but I face another problems which is when I compare the year, the stock_code is not the same. I need to have the previous year with the same stock code..please help me

SELECT cur.stock_code, cur.alert_id, cur.year_id,cur.period_id, cur.alert_value, prev.stock_code AS stock, prev.alert_id AS alert, prev.year_id AS prev_year_id,prev.period_id AS prev_period_id, prev.alert_value AS prev_alert_value
FROM company_alert AS cur
LEFT OUTER JOIN company_alert AS PREV ON cur.year_id = prev.year_id + 1 
WHERE cur.alert_id = prev.alert_id AND cur.year_id BETWEEN 2008 AND 2011
ORDER BY year_id

To "have the previous year with the same stock code" as you put it, you need to add and cur.stock_code = prev.stock_code on the criteria of your join.

Also it would make it cleaner, faster and "correct" to move the cur.alert_id = prev.alert_id in the ON part of the join. Your query should work the way you have it, but you are creating a cartesian product and then filter out the cartesian product, wasting resources and ending up with a slow query.
You should end up with this:

SELECT cur.stock_code, cur.alert_id, cur.year_id,cur.period_id, cur.alert_value, prev.stock_code AS stock, prev.alert_id AS alert, prev.year_id AS prev_year_id,prev.period_id AS prev_period_id, prev.alert_value AS prev_alert_value
FROM company_alert AS cur
LEFT OUTER JOIN company_alert AS PREV ON cur.year_id = prev.year_id + 1 
and cur.alert_id = prev.alert_id 
AND cur.stock_code = prev.stock_code
WHERE  cur.year_id BETWEEN 2008 AND 2011
ORDER BY year_id

Edited 5 Years Ago by adam_k: n/a

thanks, I've been trying the stock_code. But,

LEFT OUTER JOIN company_alert AS PREV ON cur.year_id = prev.year_id + 1 
AND cur.alert_id = prev.alert_id 
AND cur.stock_code = prev.stock_code

it seems no previous data in the query. could left outer join have many condition?

All joins can have multiple conditions.
If there are data to satisfy the conditions you should see them.

This question has already been answered. Start a new discussion instead.