Select previous field [sql]

[noobies]

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 :(

[Netcode]

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

[noobies]

yes, that's what i mean

[Netcode]

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

[noobies]

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

[noobies]

is there any error in my query?

I had problems to display it in vb.net

[adam_k]

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

[noobies]

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?

[adam_k]

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

[noobies]

thanks alot :D

[adam_k]

If this solved your problem, please mark this thread as solved.