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

Re: Select previous field [sql] 80 80

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

Re: Select previous field [sql] 80 80

yes, that's what i mean

Re: Select previous field [sql] 80 80
SELECT alert_value , year_id
from company_alert
WHERE year_id <= '2010'
GROUP BY year_id
Re: Select previous field [sql] 80 80

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
Re: Select previous field [sql] 80 80

is there any error in my query?

I had problems to display it in vb.net

Re: Select previous field [sql] 80 80

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
Re: Select previous field [sql] 80 80

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?

Re: Select previous field [sql] 80 80

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

Re: Select previous field [sql] 80 80

thanks alot :D

Re: Select previous field [sql] 80 80

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.