×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Selecting a limited number of records....

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

RE: Selecting a limited number of records....

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?

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....

Alphabetically - Hmmm - Don't know. But I have done something simliar with soundex.

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....

(OP)
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.

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....

Not too big then.

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....

(OP)
Thanks...:-)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close