The value before and after a value

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 2000 Forums

SQL Server Development (2000)

The value before and after a value

Author

Topic

cardgunner

326 Posts

Posted - 2007-09-10 : 12:08:09

If I wanted to get a list of values that was made up of the value that came before and after a specified value how could I do that?

Example. My value is bumpers.

my table has:
ball joints
bumpars
bumpers
cats paw
cazzie pail

I want to capture the one before, the value, and the one after.
bumpars
bumpers
cats paw

Can this be done? I have 3495 values I need to do this for.

I'm trying to find the ones that misspelled.

Card Gunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 12:11:05

SELECT MAX(theData) FROM Table1 WHERE theData < @WantedData UNION ALL
SELECT @WantedData FROM Table1 WHERE theData = @WantedData UNION ALL
SELECT MIN(theData) FROM Table1 WHERE theData > @WantedData

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-10 : 12:15:54

select a.s
from (select s, num = (select count(*) from tbl t2 where t2.s <= t.s) from tbl t) a
where abs(num - (select count(*) from tbl where s <= 'bumpers')) <= 1

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 12:30:07

Doesn't look that good when searching for "bumpy".

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 12:45:52

This is strange... Changing

select		a.theData
from		(
			select	theData,
				num = (select count(*) from #sample t2 where t2.theData <= t.theData)
			from	#sample t
		) a
where		ABS(num - (select count(*) from #sample where theData <= 'bumpers')) <= 1

to this

select		a.theData
from		(
			select	theData,
				num = (select count(*) from #sample t2 where t2.theData <= t.theData)
			from	#sample t
		) a
where		num - (select count(*) from #sample where theData <= 'bumpers') between -1 and 1

resulted in one extra table scan!

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2007-09-10 : 13:36:15

Peso

Thank you very nuch, however I'm not clear on what I'm supposed to do. The values I want to find the before and after for are in
view: vwnewmodels
field: Model

I added that to your script

select		a.Model
from		(
			select	Model,
				num = (select count(*) from #sample t2 where t2.theData <= vwnewmodels.Model)
			from	
		) a
where		num - (select count(*) from #sample where Model <= 'bumpers') between -1 and 1

However I'm unsure on #sample and t2. What is those supposed to be?
Also 'bunmpers'. Am I supposed to put in every value or 'Models'

Card Gunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 13:41:23

SELECT g.Model,
(SELECT MAX(h.Model) FROM vwNewModels AS h WHERE h.Model < g.Model) AS PreviousModel,
(SELECT MIN(s.Model) FROM vwNewModels AS s WHERE s.Model > g.Model) AS NextModel
FROM vwNewModels AS g

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 13:43:34

If this is not what you want, you have to post better suited extected result for more models.

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2007-09-10 : 14:00:46

Well it was enough to get me going. this is the end result. Thank you very very much.


SELECT g.modl,
(SELECT MAX(h.modl) FROM maintbl AS h WHERE h.modl < g.modl) AS PreviousModel,
(SELECT MIN(s.t_modl) FROM maintbl AS s WHERE s.modl > g.modl) AS NextModel
FROM maintbl AS g
join vwnewmodels 
on vwnewmodels.Model=g.modl

I think this is what I want. I have to see if going to help me weed oout some of the duplicates.

Thanks again.

Card Gunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources