Log in

No account? Create an account
entries friends calendar profile Elf Sternberg's Pendorwright Projects Previous Previous Next Next
Oh, mightly LJ minds, vast, cool, and unsympathetic! - Elf M. Sternberg
Oh, mightly LJ minds, vast, cool, and unsympathetic!
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.

Current Mood: annoyed annoyed
Current Music: Rammstein, Zestoeren

12 comments or Leave a comment
urox From: urox Date: July 25th, 2008 10:25 pm (UTC) (Link)
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:

I *think* it's public as I can see it in a google website. But do a search on the select-statement there.
featheredfrog From: featheredfrog Date: July 25th, 2008 10:27 pm (UTC) (Link)
Can't you index the date field?
ibsulon From: ibsulon Date: July 25th, 2008 10:40 pm (UTC) (Link)
You have just described the prototypical example for the need of a cursor.


Combine with a temporary table for your needs.
dossy From: dossy Date: July 26th, 2008 01:19 am (UTC) (Link)
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

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.
(Deleted comment)
zanfur From: zanfur Date: July 26th, 2008 03:33 am (UTC) (Link)
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 From: zanfur Date: July 26th, 2008 03:42 am (UTC) (Link)
err, that was supposed to be a response to duskwuff.

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 From: fallenpegasus Date: July 26th, 2008 05:25 am (UTC) (Link)
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 From: zanfur Date: July 26th, 2008 05:52 am (UTC) (Link)
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.
(Deleted comment)
cadetstar From: cadetstar Date: July 26th, 2008 02:02 pm (UTC) (Link)


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.

en_ki From: en_ki Date: July 26th, 2008 09:52 pm (UTC) (Link)
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.
12 comments or Leave a comment