Oh, mightly LJ minds, vast, cool, and unsympathetic!

[elfs]

Calling all SQL Geeks! I have a strange SELECT statement need. I have a table, the rows of which may be sorted in any number of ways. Regardless of the way it's sorted, I want to find the row where, say, event.date = "2008-07-05", and find the next and previous rows. The dates will not be contiguous; they may be seperated by weeks or even months, nor will the request necessarily be sorted by date.

Right now I'm creating a prev/cur/next queue and iterating through the result rows until I hit the entry I'm looking for, then iterating one more time to get the "next" entry. That hardly seems satisfactory. It seems natural to me that SQL would have something like this.

I've looked at the SELECT documentation from MySQL and there doesn't seem to be much there that would help me.

Comments

[urox.livejournal.com]

If you were using DB2, I would say use the scrollable cursors feature. I don't know if that is in other sql database products. Alternatively, see if there is a FETCH FIRST ROW or FETCH 1 ROW.

See if you can get access to the following website:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp

I *think* it's public as I can see it in a google website. But do a search on the select-statement there.

[featheredfrog.livejournal.com]

Can't you index the date field?

[ibsulon.livejournal.com]

You have just described the prototypical example for the need of a cursor.

http://dev.mysql.com/doc/refman/5.0/en/cursors.html

Combine with a temporary table for your needs.

[dossy.livejournal.com]

If you could ORDER BY event.date, then getting the current and next row is easy as:

WHERE event.date = 'some date'
ORDER BY event.date
LIMIT 2

What you're asking for, in the worst-case is O(n) - linear search aka full table scan. In other words, you can't use the WHERE clause, you have to ORDER BY event.date and iterate through the rows as you're doing. SQL is optimal when relational algebra (selection, projection, rename, etc.) can be applied to a set of data (i.e., tables).

If it doesn't seem satisfactory, it's because you're using the wrong tool (i.e., RDBMS) to solve your problem.

Ugly but effective

[duskwuff.livejournal.com]

SELECT data FROM event WHERE event.date = :target
UNION SELECT data FROM event WHERE event.date > :target LIMIT 1
UNION SELECT data FROM event WHERE event.date < :target LIMIT 1

[zanfur.livejournal.com]

That only works if the data is sorted by date, and if the date field was unique, and if you add an ORDER BY clause to the second and third SELECTs. This data may be sorted in any fashion, and he needs the previous and next for whatever sorting order.

Only way I can think of is by using a cursor.

[zanfur.livejournal.com]

err, that was supposed to be a response to zetawoof.

Anyway, I think two statements will get the job done, presuming the sort happens the same way both times:

SELECT id as targetrowid, data FROM table WHERE date = :target ORDER BY sortfield LIMIT 2
SELECT data FROM table WHERE id < :targetrowid ORDER BY sortfield DESC LIMIT 1

There are a number of variations of this that could work, using two statements. Using just one...you could possibly do it with a large three-way self-referential cross join, but it would be fugly. Subselects would be a lot cleaner to read.

[fallenpegasus]

I would put those two SELECT into a UNION, so that there is only one round trip thru the parser, planner, optimizer, and db engines.

[zanfur.livejournal.com]

I would too, if I didn't need the result of the first one to run the second. How would correlate the id returned by the first query with the second query?

I can see using a cursor and keeping track of the "last" as you iterate through, but that's ugly and slow. I can see looking up an id and then using that id to look up the immediately previous or following (or both, with a union). I can't seem to find a way of doing it in a single select or union of selects, unless there are uniqueness constraints on the date and sort order fields, which haven't been specified as unique.

Using subselects would get you what the single round-trip, but still use multiple select statements. Then, you could also just use a store procedure, too.

[duskwuff.livejournal.com]

Yeah, I forgot the ORDER BY bits. But I guess I didn't quite grasp the exact nature of the problem - if he's doing what it sounds like he's doing (grabbing the previous/next story in a thread?) then there's probably a cleaner way of doing it by restructuring the data. Would depend on what the exact purpose of the task is and what the restrictions on it are, though - if threads always run "forwards in time", then a variant on my original query would work; if they aren't guaranteed to go in the same direction, though, then something messier might be necessary.

Rails?

[cadetstar.livejournal.com]

If this is in Rails, my suggestion would be something on this nature:

@events = Event.find(:all, :conditions => {whatever}, :sort => {how you're sorting})
@mainevent = @events.find(:first, :conditions => {:date => params[:dateyourelookingfor]})
@beforevent = @events[@events.index(@mainevent) - 1]
@afterevent = @events[@events.index(@mainevent) + 1]

Though...I'm a big fan of make it work, then make it pretty so you know that it *can* be done before finding the best way to do it.

-Michael

[en-ki.livejournal.com]

It's frankly pretty strange that you want to do this, so I'm reluctant to think about how to solve that specific problem instead of thinking about whether it's the right problem to solve.