Next and prior record ? - 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 2005 Forums

Transact-SQL (2005)

Next and prior record ?

Author

Topic

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2010-03-13 : 13:49:07

Hello
my query is below. But it doesnt work truely.
i have a value for x_kod. First i order by x_kod. then i need next record from a value of x_kod. How can i do this ?

SELECT x_id, x_kod INTO #CustContact FROM " + hangitable + " ORDER BY x_kod SELECT (x_id+1), x_kod FROM #CustContact Where x_kod = " + valueTB + " ORDER BY x_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 14:22:23

can you show some sample data and then output you require out of them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2010-03-13 : 14:33:24

x_kod x_id
100 1
101 2
102 3
100.001 4
100.002 5
100.003 6
abc001 7
abc002 8

when i enter 101, i need 102 (value of x_kod is 101)
when i enter 102, i need 100.001 (value of x_kod is 102)
when i enter abc001, i need abc002

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-13 : 14:36:56

[code]
DECLARE @xkod varchar(100)
SELECT TOP 1 x_kod,x_id
FROM Table
WHERE x_id>(SELECT x_id FROM Table WHERE x_kod=@xkod)
ORDER BY x_id ASC
[/code]

pass a suitable value for @xkod like 101,102,..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2010-03-13 : 15:03:30

what will happen if data is like below ?
Id x_kod
1 102
2 abc001
3 100.001
4 101
5 100.002
6 abc002
when i enter 101, i need 102
when i enter 100.001, i need 100.002
when i enter abc001, i need abc002

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-03-13 : 17:39:30

Add a row number to your pre-sorted table is one way..probably a better way, but this works...


create table foo (
x_id int IDENTITY(1,1) Not Null
,x_kod varchar(7) not null)
 
go

Insert into foo
SELECT '102' UNION ALL
SELECT 'abc001' UNION ALL
SELECT '100.001' UNION ALL
SELECT '101' UNION ALL
SELECT '100.002' UNION ALL
SELECT 'abc002' 

go 
Create proc get_next_xkod (@xkod varchar(7))
as

Select ROW_NUMBER() OVER (ORDER BY x_kod asc) as ROW,x_kod
into #orderfoo
FROM foo 
 
Select x_kod as nextkod
FROM #orderfoo
Where ROW = (Select ROW +1 from #orderfoo where x_kod = @xkod)

Go

Exec get_next_xkod 'abc001'  ---returns abc002
go
Exec get_next_xkod '100.001'  ---returns 100.002
go
drop table foo
drop proc get_next_xkod

go

I assumed that "when I enter..." you are passing a variable to the procedure. Hope it helps. There are other ways to do this too...depending on the record count..this won't be terribly efficient but just trying to give you something to work with

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-14 : 00:23:03

quote:
Originally posted by oguzkaygun

what will happen if data is like below ?
Id x_kod
1 102
2 abc001
3 100.001
4 101
5 100.002
6 abc002
when i enter 101, i need 102
when i enter 100.001, i need 100.002
when i enter abc001, i need abc002


DECLARE @xkod varchar(100)

SELECT TOP 1 x_kod,x_id
FROM Table
WHERE x_kod>@xkod
ORDER BY x_kod ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources