Geek Answers Handbook

SQL to get a range of results in alphabetical order

I have a tblTags table that works almost the same as the StackOverflows tagging system.

When I look at a tag page, say a Tutorial tag, I want to display 10 tags before and after it in alphabetical order.

So, if we are assigned the Tutorial tag of identifier 30 , how can we return the recordset in an order resembling:

 Tap Tart > Tutorial Umbrellas Unicorns Xylaphones

I thought about how to do it badly, in my opinion, since they involve getting ugly amounts of data.

I'm not sure if it is possible to do something line by line (pseudo):

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

But how do you know the position of a textbook tag in a list in an efficient way, without going through the whole list until you find it?

I am using SQL Server 2008 R2 Express with LINQ, if that matters, SQL queries or LINQ will be great answers, thanks!

+4
source share
5 answers

Having compiled the Jacob UNION clause, you use the table variable to select the appropriate TagID, and then join the Tag table to get the corresponding entries. It is not as elegant as we would like, but it really works.

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

 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
+3
source

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

(editing: thanks to comments and other answers, especially 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 make sure it is really faster than returning more rows and then filtering down. I have a feeling that there is a more efficient method.

+3
source

According to Jacob, I had exactly the same idea, only SQL Server will take the "top 10" instead of the limit.

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

(tested this on SQL Server 2008 R2)

EDIT: As correctly noted, the previous query did not provide the required result. However, this should be a more efficient way to do the same.

 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
+2
source 
 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
+2
source

It's time to figure it out, thanks for the idea of ​​combining. Not quite sure why this works, .take(n) seems to take a lot to work, not quite sure why this is so, this code works 100% and takes 5 on 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();
0
source

More articles:


All Articles