Re: "Position To:" Design Challenge with SQL

On 12-Dec-2013 09:24 -0800, Holm, Paul wrote:

<<SNIP>>we need an SQL based solution.

To further explain the need...Assume you have a database table of
all people living in the US. We want to be able to search based on a
number of criteria such as "state". So I search for "MINNESOTA" and
I'm presented with a list/subfile of people living in MN. Example
SQL: SELECT * FROM US_PEOPLE where STATE = "MN" order by STATE,
LASTNAME

Now they want to "scroll up" in the list which should then display
people in "MICHIGAN". The issue is "MICHIGAN" is not in my result
set. If I scrolled down thru MN then I would see "Mississippi".

We are dealing with large tables so we need to make the searches
perform. We also obviously are looking for a "generic" design so it
can be used for numerous inquiry applications... Anyone have
experience?

The given example might easily be dismissed as being too unrealistic. Consider a setup instead, having reference to /items/ in /bins/ and the original equals predicate was on a specific bin-number, whereby the user thinks they know the item they want to find but do not recall the item-number [they'll just "know it when they see it"] is in bin-n but may be instead, in one of the nearby number-ordered bins... so they want to pore over the data first for bin-n and then scroll around or position to the nearby bins. Seems more plausible as a legitimate need.?

More generically, with the same specific key-inquiry comes first then scroll [or scroll to implement position], something like the following to implement the underlying work:

If result_set(K)=null then:
select * from any_file where key='KeyVal' order by key,other
-- scrollable result set (K)

If fetch_prev(K) gets EOF, then:
If result_set(LTK)=null then:
select * from any_file where key<'KeyVal' order by key,other
-- scrollable result set (LTK)
position to end (LTK)
fetch_prev(LTK)

If fetch_next(K) gets EOF, then:
If result_set(GTK)=null then:
select * from any_file where key>'KeyVal' order by key,other
-- scrollable result set (GTK)
position to top(GTK) [implicit only if just created r_s(GTK)]
fetch_next(GTK)

If fetch_next(GTK) gets EOF, then:
inform: end of data available that direction

If fetch_prev(GTK) gets EOF, then:
position to end (K)
fetch_prev(K)

If fetch_prev(LTK) gets EOF, then:
inform: end of data available that direction

If fetch_next(LTK) gets EOF, then:
position to top (K)
fetch_next(K)

-- perhaps, to avoid caller getting data and test position(key,other):
-- If not(end_of_data) and position_key not found then:
---- repeat scroll in same direction until found\passed, otherwise:
Return fetched data


The OPTIMIZE FOR N ROWS for each query is probably appropriate, to reflect how many rows can be presented via the UI.