General discussion

Locked

SQL: Get Next, Get Previous

By flintr ·
G'Day

I am currently engaged in converting a system that uses a proprietary file format for storing data to a MS-SQL Server backend.

The current application has a feature whereby the user can move forwards and backwards through the records based upon a particular field (eg if you click on the Student Surname field [say it is currently SMITH ALISON] you will get the next record SMITH ANDREW], a SHIFT-CLICK will move you back a record). The Surname and First Name components are in two seperate fields.

How can you do this using a SQL Select statement?

Cheers
Rodney

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL: Get Next, Get Previous

by Albert Franco II In reply to SQL: Get Next, Get Previo ...

One of the key differences beteen SQL type databases and "desktop" (paradox, XBase, etc.) is that there is no inherent order. Order is only established when you query.

You can only go forward and backward within the results of your query.

Picture a database with five million customers in it. You would not want to have that monster on your local machine just so that you can scroll one by one through it.

What is required here is a new way to see the problem. The data base is very large so we grab sections of it. We can move easily using GUI tools through that section but not the WHOLE database. To solve your immediate problem you might simulate the forward-backward behavior by returning a query that is wider than the target record by specifying a range. (User asks for Smith--the query returns ALL S's or All R's, S's and T's) Then the user can scroll the query results within this range. When user scrolls near the top or bottom of the range then a new query is generated and displayed.

Just remember in SQL you never have the whole DB on the client machine only the part of it that came back with the last query. This is a fundamental difference--it calls for some serious thought about redisigning user interfaces which reliedon having the whole DB on hand for every operation (which is fine for small datasets but impossible for big ones)

Good Luck

Collapse -

SQL: Get Next, Get Previous

by flintr In reply to SQL: Get Next, Get Previo ...

Poster rated this answer

Collapse -

SQL: Get Next, Get Previous

by andrew In reply to SQL: Get Next, Get Previo ...

Here's a way using the 'top' predicate and the order by clause:
(this example uses the 'pubs' database from SQL Server 7)

First, get the first record:

select top 1 * from authors order by au_lname

To get the next record, pass back the au_lname value (in this case 'Bennet'):

select top 1 * from authors where au_lname > 'Bennet' order by au_lname

to get the previous record:

select top 1 * from authors where au_lname < 'Blotchet-Halls' order by au_lname

which gets you back tothe first record.

Hope this helps.

Collapse -

SQL: Get Next, Get Previous

by flintr In reply to SQL: Get Next, Get Previo ...

Poster rated this answer

Collapse -

SQL: Get Next, Get Previous

by flintr In reply to SQL: Get Next, Get Previo ...

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums