MySQL Sort Question

One of those issues where I have a feeling somebody's excellent suggestion will make me wonder how I could have not figured it out myself...

I'm creating a database full of records representing reported cases. A major means of sorting and accessing the records will be by print reporter citation, and I want to have "Next Case" and "Previous Case" links in the record display. The nature of the workflow probably makes it unreasonable to expect that these records will be entered in reporter citation sequence, so auto-incrementing record numbers won't do the job of performing that particular sort. So consider the following three-in-a-row reported cases:

301 Va. 28
301 Va. 28 (a second case starting on the same page as the one before)
301 Va. 41

It occurs to me to script the creation of numeric field entries representing these citations. In this example, maybe 301028000, 301028001, and 301041000, respectively (which should work fine since these reporter volumes are nowhere near 1000 pages). That would give me my sort. The short question is: If I'm displaying the second record above, how do I phrase MySQL queries to retrieve the "one before" and the "one after?"

I figure the work-around would be to require the user to pre-enter a whole volume's citations in correct order so that record numbers will work, but there are reasons that approach may not be doable.

TIA,

Paul

Paul Birch
University of Richmond School of Law
pbirch@richmond.edu

MySQL Sort Question

Say you're displaying record with sort_number=301028001, you can do it
in two queries like this:

The "one before":
SELECT * FROM table
WHERE sort_number < 301028001
ORDER BY sort_number DESC LIMIT 1

The "one after":
SELECT * FROM table
WHERE sort_number > 301028001
ORDER BY sort_number ASC LIMIT 1

-Stuart

On Thu, Apr 30, 2009 at 2:27 PM, Birch, Paul <pbirch@richmond.edu> wrote:
> 301 Va. 28
>
> 301 Va. 28 (a second case starting on the same page as the one before)
>
> 301 Va. 41
>
> It occurs to me to script the creation of numeric field entries representing
> these citations.  In this example, maybe 301028000, 301028001, and
> 301041000, respectively (which should work fine since these reporter volumes
> are nowhere near 1000 pages).  That would give me my sort.  The short
> question is: If I’m displaying the second record above, how do I phrase
> MySQL queries to retrieve the “one before” and the ”one after?”
_______________________________________________
You are currently subscribed to teknoids as: tekarchive@host2.teknoids.net.
To unsubscribe send a blank email to teknoids-leave@ruckus.law.cornell.edu
--
See the web interface at http://ruckus.law.cornell.edu/mailman/listinfo/teknoids to get your list password, unsubscribe, and view your list settings.

RE: MySQL Sort Question

Ah of course-- "LIMIT 1." (Just as I predicted: I can't believe I didn't figure that out.)

Thanks, Stuart.

Paul

-----Original Message-----
From: teknoids-bounces@ruckus.law.cornell.edu [mailto:teknoids-bounces@ruckus.law.cornell.edu] On Behalf Of Stuart Sierra
Sent: Thursday, April 30, 2009 2:43 PM
To: Teknoids
Subject: Re: [teknoids] MySQL Sort Question

Say you're displaying record with sort_number=301028001, you can do it
in two queries like this:

The "one before":
SELECT * FROM table
WHERE sort_number < 301028001
ORDER BY sort_number DESC LIMIT 1

The "one after":
SELECT * FROM table
WHERE sort_number > 301028001
ORDER BY sort_number ASC LIMIT 1

-Stuart

On Thu, Apr 30, 2009 at 2:27 PM, Birch, Paul <pbirch@richmond.edu> wrote:
> 301 Va. 28
>
> 301 Va. 28 (a second case starting on the same page as the one before)
>
> 301 Va. 41
>
> It occurs to me to script the creation of numeric field entries representing
> these citations.  In this example, maybe 301028000, 301028001, and
> 301041000, respectively (which should work fine since these reporter volumes
> are nowhere near 1000 pages).  That would give me my sort.  The short
> question is: If I’m displaying the second record above, how do I phrase
> MySQL queries to retrieve the “one before” and the ”one after?”
_______________________________________________
You are currently subscribed to teknoids as: pbirch@richmond.edu.
To unsubscribe send a blank email to teknoids-leave@ruckus.law.cornell.edu
--
See the web interface at http://ruckus.law.cornell.edu/mailman/listinfo/teknoids to get your list password, unsubscribe, and view your list settings.