ORA-30484: missing window specification for this function

please try this:

select lead(id) over(order by start_time desc) lead_id
from tablename
 where id=8193 
 group by station
 order by start_time desc;

Select allOpen in new window

for the reference:
http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php

i did this
select lead(id) over(order by started desc) lead_id
from tablename
where id=18193
group by station,started,test_id
order by started desc;
and i didnt get any output ..what does this signify?

ok i managed to print..

select serial, lead(ended) over(order by station,started desc)
 from tablename
 where test_id=18193;

and i got the serial; but it the same row...not the next. i mean the record is for the same row as the id not the prev or next one.

can you show sample data, please? we are blind otherwise :)

ok sorry
here is the sample data
Serial      Test id      Station      Status      Started                           Ended
5297      20553      TEST1      GOOD      8/4/2008 7:35      8/4/2008 10:45
5180      18933      TEST1      ERROR      8/4/2008 6:04      8/4/2008 6:55
6017      18193      TEST1      GOOD      8/4/2008 1:12      8/4/2008 4:12
5938      15743      TEST1      GOOD      8/3/2008 18:16      8/3/2008 21:20

any help

yes, if you now show us what resulting data you want?

ok can u help me get any one id... i ve put 18193; i need any one prev or next.18933      or 15743      ???????

5180      18933      TEST1      ERROR      8/4/2008 6:04      8/4/2008 6:55
6017      18193      TEST1      GOOD      8/4/2008 1:12      8/4/2008 4:12
5938      15743      TEST1      GOOD      8/3/2008 18:16      8/3/2008 21:20

based on the id i want to get the next id ... either use lag or lead i am not worried.

also when i put this query in a pl/sql i get this error
Error(145,38): PL/SQL: ORA-30483: window  functions are not allowed here

cant i run this query in pl/sql...?

that request is NOT what LAG or LEAD are for.
those functions get you the data for the previously returned row, not from the previous row.
so, you need the next row:

select t.*
, ( select min(i.id) from tablename i where i.station = t.station and i.id > t.id ) next_id
from tablename t
where t.id = 18193

Select allOpen in new window

i cant use min/max...
i dont need the min and max id...

i can create a view sorted by started desc and grouped by station. all i want to know id supposing i am on rownum 1000... i want to know what is the id for 999th row...
i hope i m clear :-(

well, then this should explain how it works:

select t.serial current_serial
 , lead(serial) over(partition by station, order by started desc) lead_serial
 , lag(serial) over(partition by station, order by started desc) lag_serial
 , t.*
 from tablename t
 where t.station = 'TEST1';

Select allOpen in new window

if later you want to have for 1 specific station/id:

with l as ( select t.serial current_serial
 , lead(serial) over(partition by station, order by started desc) lead_serial
 , lag(serial) over(partition by station, order by started desc) lag_serial
 , t.*
 from tablename t
 where t.station = 'TEST1')
select l.* from l where l.id = 18193
;

Select allOpen in new window

can u tell me why i am getting this error
lead(serial) over(partition by station, order by started desc)
                                                                        *
ERROR at line 1:
ORA-00936: missing expression

nope the result is same as without the partition. i am not able to understand why this is happening.
its returning me the same row details whose id i put in the condition. i want one b4 that or next one...

sorry, but you must be doing something wrong.
I tested the function just to be 100% sure, and it DOES work just fine.

so:
lead_serial and lag_serial MUST be evaluating to another row's serial number !!!

please show what you are "hiding".

i got it running. the mistake i was doing was i was tryign to print the serial rather than lead_serial..
wonderful..................!
thanks sooooooooo much. really gr8ful

COOOL.
so now, next question :)