Selecting a limited number of records....

[DarkMan]

I'm working on a project where I need to be able to input a name, and from a list, get the 10 names that come before it alphabetically and the 10 names that come after it. Is there any way I can write a query that will only return these records?<br>
<br>
I know I can retrieve all the records and then sort them in my application, but, due to the length of the table, I would rather only return the 21 records I want to work with.<br>
<br>
Thanks in advance...<br>
-Doug

 

[JimmyDix]

I thought this interesting, so I had a go at it. Problem is, I don't know how to create an incremental number, like autonumber in MS Access. I have put it in here as 'inc', anyone know how to do this?<br>
<br>
The theory is thus: create a view with all the data from the table you want, plus a field that numbers the fields. Then, use a procedure to get the line number of the name you want, and get the previous 10 and following 10. Should work, but I'm just a learner, so don't shoot me for the bad syntax e.g.:<br>
<br>
CREATE VIEW staffmembers_view<br>
AS<br>
SELECT list_number = inc, name, address, phone<br>
FROM staffmembers<br>
ORDER BY name<br>
<br>
GO<br>
<br>
CREATE PROCEDURE get21 @thename char(30)<br>
AS<br>
BEGIN<br>
<br>
define @thenum int<br>
<br>
@thenum = (SELECT list_number FROM staffmembers_view WHERE name = @thename)<br>
<br>
SELECT *<br>
FROM staffmembers_view<br>
WHERE list_number IN<br>
(SELECT list_number<br>
FROM staffmembers_view<br>
WHERE list_number BETWEEN (@thenum - 10) AND (@thenum + 10))<br>
<br>
RETURN<br>
END

 

[MikeLacey]

Alphabetically - Hmmm - Don't know. But I have done something simliar with soundex.<br>
<br>
Oracle lets you index on the soundex value of a string. (I know you use SQL Server but bear with me)<br>
<br>
Soundex returns a number (N) from a string. Count the records the match (Between N-10 and N+10) and see if that returns about the right number of rows.<br>
<br>
Alternatively: You might be able to work your way backwards and forwards in a dyna/recordset as long as your table isn't *too* big. How big is it?<br>
<br>
Mike<br>

 

[DarkMan]

Well, the table is expected to grow to be at least 500,000 records long... I looked at the possibility of using cursors, but I was informed that SQL Server doesn't handle these very well, and they tend to bring the system to a crawl. Since I will be making new sites using the same database skeleton, the same routine would have to work on a small table as well as the large one.<br>
<br>
I also thought that using the "Like" comparison might work. Basically compare the first four letters, and if it didn't return at least 20 records, compare the first three letters, etc, down to one letter, and then to the adjacent letters. I'm not sure if this would speed things up, though, since in the new databases, I would probably require 4-5 queries to nail down the proper number of returned entries.<br>
<br>
What do you think?

 

[MikeLacey]

Not too big then.<br>
<br>
Your method looks ok, I've probably got the same "it's not very elegant" reservations about it that you have but I can't really improve on it at first glance.<br>
<br>
I'll have a think.<br>
<br>
-ml<br>

 

[DarkMan]

Thanks...