Need rows BETWEEN, but not really

Question

Need rows BETWEEN, but not really

andy.gear

SSC-Addicted

Points: 493
More actions
January 20, 2010 at 9:58 am

#140720

I have a table called Schmoo and here is what Schmoo looks like:
W, X, Sequence, Z (field names)
1,1,1,text
1,1,2,><
1,1,3,text
1,1,4,text
1,1,5,><
1,1,6,text
4,5,1,><
4,5,2,text
4,5,3,><
4,5,4,text
I want the rows between the first >< and the second >< for a certain W,X.
Any help would be appreciated!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

In order for you to know what is between the values, you must specify how they are ordered. Is it safe to say that each WX value is ordered by Sequence?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

John Rowan SSC Guru Points: 56440 More actions · Answer 2

If so, try this:

DECLARE @Table TABLE (W int, X int, Sequence int, Z varchar(10))

INSERT INTO @Table

SELECT 1,1,1,'text' UNION ALL

SELECT 1,1,2,'><' UNION ALL

SELECT 1,1,3,'text' UNION ALL

SELECT 1,1,4,'text' UNION ALL

SELECT 1,1,5,'><' UNION ALL

SELECT 1,1,6,'text' UNION ALL

SELECT 4,5,1,'><' UNION ALL

SELECT 4,5,2,'text' UNION ALL

SELECT 4,5,3,'><' UNION ALL

SELECT 4,5,4,'text'

SELECT t.*

FROM @Table t

INNER JOIN (

SELECT W,

X,

MIN(Sequence) as StartingPoint,

MAX(Sequence) as EndingPoint

FROM @Table

WHERE Z = '><'

GROUP BY W,X

) t2

ON t.W = t2.W AND t.X = t2.X

AND t.Sequence > t2.StartingPoint

AND t.Sequence < t2.EndingPoint

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

andy.gear SSC-Addicted Points: 493 More actions · Answer 3

That's a correct assumption and it works perfectly, thank you very much! With the inclusion of a where clause I can even pick the text for a particular W,X which is exactly what I wanted.