Selecting a limited number of records....
Selecting a limited number of records....
(OP)
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?
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.
Thanks in advance...
-Doug
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.
Thanks in advance...
-Doug
RE: Selecting a limited number of records....
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.:
CREATE VIEW staffmembers_view
AS
SELECT list_number = inc, name, address, phone
FROM staffmembers
ORDER BY name
GO
CREATE PROCEDURE get21 @thename char(30)
AS
BEGIN
define @thenum int
@thenum = (SELECT list_number FROM staffmembers_view WHERE name = @thename)
SELECT *
FROM staffmembers_view
WHERE list_number IN
(SELECT list_number
FROM staffmembers_view
WHERE list_number BETWEEN (@thenum - 10) AND (@thenum + 10))
RETURN
END
RE: Selecting a limited number of records....
Oracle lets you index on the soundex value of a string. (I know you use SQL Server but bear with me)
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.
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?
Mike
RE: Selecting a limited number of records....
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.
What do you think?
RE: Selecting a limited number of records....
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.
I'll have a think.
-ml
RE: Selecting a limited number of records....