SQL to get range of results in alphabetical order

I have a table, tblTags which works in much the same way as StackOverflows tagging system.

When I view a tags page, let's say the tag Tutorial I want to display the 10 tags before and after it in alphabetical order.

So if we are given the tag Tutorial of ID 30 how can we return a record 开发者_如何学Cset in the order resembling:

Tap
Tart
> Tutorial
Umbrellas
Unicorns
Xylaphones

I have thought of ways of doing this, badly, in my opinion as they involve retrieving ugly amounts of data.

I'm not sure if it's possible to do something along the lines of (pseudo):

SELECT RANGE(0 - 30) FROM tblTags ORDER BY Name ASC

But how do you know the position of the tutorial tag in the list in an efficient manner without traversing the entire list until you find it?

I'm using SQL Server 2008 R2 Express with LINQ if it makes any difference, SQL queries or LINQ would be great answers, thanks!

---

Building off Jacob's UNION suggestion, you use a table variable to select the matching TagID's into and then join back against the Tag table to get the matching records. It's not as elegant as I would like, but it does work.

As a side note, I think the UNION approach would work, but AFAIK SQL Server only allows ORDER BY on the last SELECT, and that ORDER BY applies to the entire result set (this post also states the same thing).

DECLARE @tags AS TABLE(TagID INT, Name VARCHAR(30))
INSERT INTO @tags VALUES(1, 'a')
INSERT INTO @tags VALUES(2, 'b')
INSERT INTO @tags VALUES(3, 'c')
INSERT INTO @tags VALUES(4, 'd')
INSERT INTO @tags VALUES(5, 'e')
INSERT INTO @tags VALUES(6, 'f')
INSERT INTO @tags VALUES(7, 'g')
INSERT INTO @tags VALUES(8, 'h')
INSERT INTO @tags VALUES(9, 'i')
INSERT INTO @tags VALUES(10, 'j')

DECLARE @selectedTags AS TABLE(TagID INT)
INSERT INTO @selectedTags
SELECT TOP 2 TagID FROM @tags WHERE Name < 'e' ORDER BY Name DESC
INSERT INTO @selectedTags
SELECT TOP 2 TagID FROM @tags WHERE Name >= 'e' ORDER BY Name

SELECT * 
FROM @tags T
JOIN @selectedTags ST ON ST.TagID = T.TagID
ORDER BY T.Name

---

Maybe a union will work. (I'm sure I have some syntax errors, but you get the idea)

(edit: thanks to comments and other answers especially by rsbarro)

---

DECLARE @tags AS TABLE(TagID INT, tag VARCHAR(30))
INSERT INTO @tags VALUES(1, 'a')
INSERT INTO @tags VALUES(2, 'b')
INSERT INTO @tags VALUES(3, 'c')
INSERT INTO @tags VALUES(4, 'd')
INSERT INTO @tags VALUES(5, 'e')
INSERT INTO @tags VALUES(6, 'f')
INSERT INTO @tags VALUES(7, 'g')
INSERT INTO @tags VALUES(8, 'h')
INSERT INTO @tags VALUES(9, 'i')
INSERT INTO @tags VALUES(10, 'j');

select * from (select top(2) tag
from @tags
where tag < 'f'
order by tag desc
) a

union

select * from (select top(3) tag
from @tags
where tag >= 'f'
order by tag) b;

However I would do performance checks to see if this indeed faster than returning more rows and then filtering down. I have a feeling that there is a more performant method.

---

With X As (select tblTag.*, Row_Number() Over(Order By tag) R_NUMB From tblTag )
Select tag From X
Where X.R_NUMB  Between (Select X.R_NUMB From X  Where tag = 'Tutorial') - 10 
And (Select X.R_NUMB From X  Where tag = 'Tutorial') + 10

---

As Jacob suggests, I had precisely the same idea, only SQL Server will take "top 10" instead of limit.

select top 3 * 
from names
where name<'d'
union
select top 3 * 
from names
where name>='d'
order by name 

(tested this one on SQL Server 2008 R2)

EDIT: As correctly noted, the previous query did not provide the required result. This one should, however, there might be more efficient way of doing the same thing.

select name from names
where name in (
    select top 3 name from names where name<'e' order by name desc
    )or name in (
    select top 3 name from names where name>='e')
order by name 

---

Took me a while to figure it out, thanks for the union idea. Not quite sure why this works, the .take(n) seems to take a large number to work not quite sure why that is, this code seems to work 100% fine and will take 5 from each side:

var q = (
    from c in db.tblTags
    where
        !(from o in db.tblTagSynonyms
            select o.SynonymTagID)
        .Contains(c.ID)
        &&
        c.Name.CompareTo(AnchorTagName) < 1
    orderby c.Name descending
    select new
        {
            c.ID,
            c.Name,
            c.Priority,
            TotalResources = (from a in db.tblTagAnchors where a.TagID == c.ID select new { a.ID }).Count()
        }
    ).Take(7).ToArray().Reverse().Union(
    from c in db.tblTags
    where
        !(from o in db.tblTagSynonyms
            select o.SynonymTagID)
        .Contains(c.ID)
        &&
        c.Name.CompareTo(AnchorTagName) >= 1
    orderby c.Name ascending
    select new
    {
        c.ID,
        c.Name,
        c.Priority,
        TotalResources = (from a in db.tblTagAnchors where a.TagID == c.ID select new { a.ID }).Count()
    }
).Skip(1).Take(11).ToArray();