Oracle Lead() function in Sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Oracle Lead() function in Sql

Author

Topic

7siva7
Starting Member

28 Posts

Posted - 2011-11-24 : 07:11:40

HI all

i got this line from Oracle SP

select ISNULL(LEAD(C_Id) OVER (ORDER BY P_Id, R_Group_NUM ASC),-1) AS Next_C
from My_Table

How can i rewrite it to SQL SErver

is there any Function in Sql that can do this work?

pelase help me

thanks in advance

HI

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 07:16:33

think you would have to use row_number() to order the rows in a cte then join that cte to itself use the row number and row number + 1.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

7siva7
Starting Member

28 Posts

Posted - 2011-11-24 : 07:17:43

can you give me the syntax please ...i am new to sql server

HI

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 07:26:36

maybe

with cte as
(
select C_id, seq = ROW_NUMBER() over (ORDER BY P_Id, R_Group_NUM)
)
select t1.C_id, coalesce((select C_id from cte t2 where t1.seq+1 = t2.seq), -1) as Next_C
from cte t1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

7siva7
Starting Member

28 Posts

Posted - 2011-11-24 : 07:35:15

Are you sure ?

is this code will work with same functionality as LEAD() In Oracle?

HI

7siva7
Starting Member

28 Posts

Posted - 2011-11-24 : 07:43:03

I executed this code

select sno from first_table;

with cte as
(
select sno,seq=ROW_NUMBER() over (order by name) from first_table
)
select t1.sno,coalesce((select sno from cte t2 where t1.seq+1 =t2.seq),-1) as next_c from cte t1

output is as follows

sno
----
1
2
3

sno next_c
--- -----
1 3
3 2
2 -1

can you explain what actually done?

i didnt understand ..please

HI

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-24 : 07:56:14

It's just what I think the lead statement does - I could be wrong.
What were you expecting?

That should give the sno and the sno from the next row ordered by name.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources