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

Posted by MaineBob OConnor on 04/16/02 03:53 PM

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

2: Response to SQL tip: Find NEXT in Result (response to 1)

Posted by David Walker on 04/16/02 04:17 PM

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

3: Response to SQL tip: Find NEXT in Result (response to 1)

Posted by Don Baccus on 04/16/02 04:19 PM

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.

4: Response to SQL tip: Find NEXT in Result (response to 1)

Posted by Jonathan Ellis on 04/16/02 05:29 PM

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.

5: Response to SQL tip: Find NEXT in Result (response to 1)

Posted by MaineBob OConnor on 04/16/02 06:00 PM

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

6: Response to SQL tip: Find NEXT in Result (response to 1)

Posted by Jonathan Ellis on 04/16/02 06:20 PM

just call db_resultrows.

7: Response to SQL tip: Find NEXT in Result (response to 1)

Posted by Jonathan Ellis on 04/16/02 06:23 PM

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.