Forum OpenACS Q&A: SQL tip: Find NEXT in Result

Collapse
Posted by MaineBob OConnor on

I'm looking for a simple "non-kludgy" way to get the NEXT item in an SQL Result list. For example this list was ordered by name. and I currently have id 5 for Bob. Now I want to do the same SQL query again and get the next item afer Bob, 8 Cathy:

ID  Name
---------
87  Alice
5   Bob
8   Cathy
93  David
33  Eve
...

I could use a TCL loop with a
while { [ns_db getrow $db $selection] } {
and set a flag when I reach ID 5, do one more loop to get ID 8 then exit and present the edit page for ID 8

Is there a quicker and more elegant way within PG SQL to get the same answer?

I'm using this so that when editing an individual on one webpage, the user can go to edit the next individual in the result list by clicking "Save and Next" button instead of going back to that list to click the "Edit" Button.

Currently they can't press the back button without an extra click on the "Retry" because this is form post data... "This form cannot be refreshed without resending...". So I want them to skip the list entirely and just go to the next individual item and edit it.

TIA
-Bob

Collapse
Posted by David Walker on
You have already order the list by name. Now just select where name > current name. For example:
select user_id,first_names
 from users
 where upper(first_names) > upper('Bob')
 order by upper(first_names)
 limit 1
Collapse
Posted by Don Baccus on
select id, name
from your_table
order by id
limit 1 offest :current_offset
where "current_offset" is a Tcl variable containing the number of the row you're interested in.

It's a bit more complicated in Oracle but since you said you're using PG I won't bother with an example.

Collapse
Posted by Jonathan Ellis on
David's is more robust; with Don's, an insert in between your two selects could result in the second select returning a name you've already seen.
Collapse
Posted by MaineBob OConnor on

David, I like your answer.... It would work with repeated clicks on my "Save and Next" button.

Don, (To help with my learning and for future use) I see that I could use a TCL count variable in my loop to know the offset but is there a way to know the number of the row returned by pg, some internal PG variable like oid and in this example "pg_count":

select first_names, user_id, pg_count from users where...
Then I could use pg_count+1 in the offset in a later query.

Thanks
-Bob

Collapse
Posted by Jonathan Ellis on
just call db_resultrows.
Collapse
Posted by Jonathan Ellis on
whoops, misread the question.

there isn't an exact analog to oracle's rownum in PG.  so in this case you'd just have to keep a counter in your db_foreach that reads the rows returned.