By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,035 Members | 1,543 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,035 IT Pros & Developers. It's quick & easy.

lead and lag problem

100+
P: 138
hi all,
i'm using postgresql 8.0.0 and i want to use the lead and lag window functions but it says "function doesn't exist when i run the following command
select lag(selling_rate,1,'0') from currency

and i again run the following command
select selling_rate, lead(selling_rate) over ( order by selling_rate asc) from currency
then it throws the error "syntax error at or near "over" at character 41"

is there any other way to use lead or lag.
TIA
Apr 19 '10 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 700
Widow functions appear in 8.4. You can't use it in 8.0
Apr 19 '10 #2

100+
P: 138
@rski
thank you for the reply.

can you please suggest me what shall i do in 8.0 to use such functions. i mean any other method to do so???
Apr 20 '10 #3

Expert 100+
P: 700
You will need some plpgsql function or postgres 8.4.
Put here the table structure and what do you want to achive.
Apr 20 '10 #4

100+
P: 138
i have a table currency with fields
id serial,
currency_id varchar(30),
selling_rate numeric(12,2),
updated_date timestamp.

what i need is a report to show the details of the currency_id as
currency_id prev selling_rate curr. selling_rate
update_date


i ihave data in my table as

id currency_id selling_rate updated_date
1 1 20.00 2010-03-20 15:30:46
2 1 22.00 2010-03-25 11:20:22
3 1 21.22 2010-03-28 09:11:20

what i want to show is like this:

currency_id prev selling_rate curr. selling_rate update_date
1 0.00 20.00 2010-03-20 15:30:46
1 20.00 22.00 2010-03-25 11:20:22
1 22.00 21.22 2010-03-28 09:11:20


i think u understand what i mean to show in my report..

TIA
Apr 20 '10 #5

Expert 100+
P: 700
What about that?
Expand|Select|Wrap|Line Numbers
  1. select 
  2.          t1.currency_id,
  3.          case when t2.selling_rate is null then 0 else t2.selling_rate end as previous,
  4.          t1.selling_rate,
  5.          t1.update_date 
  6. from 
  7.       test t1 left join (select * from test) t2 on 
  8.              (t2.id=(select max(id) from test where id<(t1.id)) and               
  9.               t1.currency_id=t2.currency_id) 
  10. order by t1.id ;
  11.  
Apr 20 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.