0

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

3
Contributors
10
Replies
11
Views
6 Years
Discussion Span
Last Post by adam_k
-1

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

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

Edited by Netcode: n/a

0

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
0

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 by adam_k: n/a

0

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?

0

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.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.