How can I search for rows "around" a given string value?

Question

I have a table with a varchar() field called identifier that contains a unique name for the entity.

I'd like to search the table and find the rows with a given identifier and up to 10 rows on either side of that identifier, sorted alphabetically (i.e., the searched-for identifier and its neighboring rows on either side).

What is the best way to formulate this in SQL Server 2005? I'm guessing there's some ROW_NUMBER() magic that can do this, but I'm not finding any go-by queries to do this sort of thing.

This is as close as I can get so far, but performance is terrible:

WITH 
  allrows AS (
   SELECT *, ROW_NUMBER() OVER(ORDER BY identifier DESC) AS RN
       FROM mytable
  ),
  centerrow AS (
    SELECT RN AS CRN FROM allrows WHERE identifier = 'MyValue'
  )
SELECT * FROM allrows, centerrow 
WHERE RN BETWEEN (centerrow.CRN - 10) AND (centerrow.CRN + 10)

The table has over 2 million records, and the identifier field can be as long as 1000 characters.

score 5 · Answer 1 · answered Feb 16 '12 at 23:11

5

SELECT TOP 11 * FROM MyTable WHERE identifier >= 'My Value' ORDER BY identifier ASC
UNION ALL
SELECT TOP 10 * FROM MyTable WHERE identifier < 'My Value' ORDER BY identifier DESC

answered Feb 16 '12 at 23:11

Pittsburgh DBA

6,672
2
39
68

Might want to have both ascending and descending indexes on the `identifier`. – HABO Feb 17 '12 at 00:33

score 2 · Accepted Answer · answered Feb 17 '12 at 09:26

2

This answer is a bit more compilerfriendly

declare @e table(identifier varchar(5))
insert @e values (0),(1),(2),(3),(4)

SELECT * FROM(
SELECT top 2 * 
FROM @e WHERE 
identifier >= '2' 
ORDER BY identifier asc) a
UNION ALL 
SELECT * FROM (
SELECT top 1 * 
FROM @e 
WHERE identifier < '2' 
ORDER BY identifier desc ) b

answered Feb 17 '12 at 09:26

t-clausen.dk

43,517
12
59
92

I hadn't considered a UNION, my head was stuck in trying to do it within a single SELECT. I chose this answer over the other similar one since it also handles sorting the final result set. – richardtallent Feb 17 '12 at 15:11
What makes this more "compiler friendly"? I missed the sort requirement, but I cannot see how this is any different, "compiler-friendly"-wise, than mine. In fact, other than adding the sort requirement, it may as well be copypasta. – Pittsburgh DBA Feb 17 '12 at 15:37
@PittsburghDBA your script does not compile, I don't mind you getting the point. But your script is invalid as it is. – t-clausen.dk Feb 17 '12 at 18:11
Haha - I see what I did there; I put ORDER BY in the SELECTs. My bad. I really should type these in a proper editor instead of the answer box. Bleh. Maybe next time you could say "The other solution won't compile". I thought you were implying optimization rather than actual compilation. Silly me. – Pittsburgh DBA Feb 17 '12 at 18:17
@PittsburghDBA I was also considering just fixing your scipt instead. It was almost there. I vote you up instead for having the right idea. – t-clausen.dk Feb 17 '12 at 18:24

How can I search for rows "around" a given string value?

2 Answers2

Linked