Next or Previous VARCHAR - 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 or Previous VARCHAR

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-09-19 : 13:20:20

I need a way to get the next part_id that exists in my table. Here is a short example


DECLARE @P AS VARCHAR(15)
SET @P = '211040297'


CREATE table #TEMP (part_id varchar(20))
insert	#TEMP values	('211040297')
insert	#TEMP values	('211040297L')
insert	#TEMP values	('211040297R')

SELECT 
	TOP 1 
	part_id
FROM 
	#TEMP
WHERE 
	part_id > @P

drop table #TEMP

This selects 211040297L just as I need for the "next" varchar.. however when I reverse the symbol I don't get the same results


DECLARE @P AS VARCHAR(15)
SET @P = '211040297R'


CREATE table #TEMP (part_id varchar(20))
insert	#TEMP values	('211040297')
insert	#TEMP values	('211040297L')
insert	#TEMP values	('211040297R')

SELECT 
	TOP 1 
	part_id
FROM 
	#TEMP
WHERE 
	part_id < @P

drop table #TEMP

Selects 211040297 when I need it to select 211040297L

Any slick way of doing this? I've tried casting the fields in various ways, but my knowledge limits me further. Any help is appreciated, Thanks!

Kristen
Test

22859 Posts

Posted - 2007-09-19 : 13:25:23

You need an ORDER BY if you want the TOP to use a "repeatable" sequence, otherwise its effectively Random.

Kristen

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-09-19 : 13:29:52

Perfect-o thanks much!!!


DECLARE @P AS VARCHAR(15)
SET @P = '211040297R'


CREATE table #TEMP (part_id varchar(20))
insert	#TEMP values	('211040297')
insert	#TEMP values	('211040297L')
insert	#TEMP values	('211040297R')


SELECT 
	TOP 1 
	part_id
FROM 
	#TEMP
WHERE 
	part_id < @P
order by part_id desc

drop table #TEMP

Selects 211040297L as expected.

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-09-19 : 13:47:37

Please note that the "next" value returned is not in the order in which you inserted them into the table... its merely sorted by the part_id on the way out.

quote:
Selects 211040297L as expected.

I imagine R & L are Right and Left... so if you added M (Middle), what you consider "next" and what is ordered by your query may not match.

Nathan Skerl

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2007-09-19 : 14:03:34

You're right


DECLARE @P AS VARCHAR(15)
SET @P = '211040297R'


CREATE table #TEMP (part_id varchar(20))
insert	#TEMP values	('211040297')
insert	#TEMP values	('211040297L')
insert	#TEMP values	('211040297R')
insert	#TEMP values	('211040297M')


SELECT 
	TOP 1 
	part_id
FROM 
	#TEMP
WHERE 
	part_id < @P
order by part_id desc

drop table #TEMP

This selects 211040297M, which is actually what I need to happen. The parts definatly aren't inserted in a particular order from the system I'm selecting from, but I do need this Prev/Next system to select them this way.

Kristen
Test

22859 Posts

Posted - 2007-09-19 : 15:11:13

"The parts definatly aren't inserted in a particular order from the system I'm selecting from"

Just for the avoidance of doubt, there is a very important point here.

EVEN IF they WERE inserted in a particular order that has NOTHING to do with the order that they will be retrieved in.

And WORSE than that, 90+% of the time they WILL be retrieved in that order (or the order imposed by a clustered index, which is probably "what you want"), so in testing it will probably LOOK correct

HOWEVER, SQL Server will use a bunch of tricks to speed up queries, one of which is to use partial data, which satisfies part of a query, and which happens to be in memory [e.g. from some previous query]. whilst it is getting the remaining data from disk.

In this somewhat rare situation the cached-data will be delivered first [absent any ORDER BY statement] which will COMPLETELY spoil your day if you have NO Order By clause ...

... and as I am often heard to say on here I WISH THAT SQL Server HAD A Lint-Like WARNING SYSTEM FOR THIS TYPE OF ACCIDENTAL PROGRAMMER MISTAKE.

(Shouting just in case Microsoft are listening but with only half an ear open!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources