getting the record, and the one before and the one after

sfullman

OK, suppose I have these records:

st_code

AK
AL
AR
CA
CT

I can order by st_code in my query. What I want to get is three rows. Suppose I know CA is in the table. I don't know that AR is before or CT is after. but I want to get this result:

AR
CA
CT

is there any elegant query method to do this in one query? Maybe the absolute of binary values subtracted? I know some of you guys deal with this all the time.

Again, I don't theoretically KNOW the previous or next alpha sort values, so I can't say "WHERE st_code between 'AR' and 'CT'" - it has to be in the sorting itself.

P.S. Don't care what order I get the three rows in, that much I can sort

Derokorian

(SELECT st_code FROM states WHERE st_code > 'CA' ORDER BY st_code ASC LIMIT 1)
UNION ALL
(SELECT st_code FROM states WHERE st_code < 'CA' ORDER BY st_code DESC LIMIT 1)

You already know the middle row, but you could always just add a where st_code = 'CA' line in there, dropping the order by.

sfullman

blehhh.. I hate unions! They are so unmathematical.

Having said that, I'll take it 🙂

I would think it necessary to modify as follows though assuming we don't know if the next/preceding element is present:

(SELECT 1, st_code FROM states WHERE st_code > 'CA' ORDER BY st_code ASC LIMIT 1)
UNION ALL
(SELECT 0, st_code FROM states WHERE st_code > 'CA' ORDER BY st_code ASC LIMIT 1)
UNION ALL
(SELECT -1, st_code FROM states WHERE st_code < 'CA' ORDER BY st_code DESC LIMIT 1)

thanks for your help, this will streamline my coding a bit in a few cases

Derokorian

(SELECT 1, st_code FROM states WHERE st_code > 'CA' ORDER BY st_code ASC LIMIT 1)
UNION ALL
(SELECT 0, st_code FROM states WHERE st_code > 'CA' ORDER BY st_code ASC LIMIT 1)

This will give you the same st_code one paired with 0 and one paired with 1. I think the 3 statements you would want would be:

(SELECT 1, st_code FROM states WHERE st_code > 'CA' ORDER BY st_code ASC LIMIT 1)
   UNION ALL
(SELECT 0, st_code FROM states WHERE st_code = 'CA' LIMIT 1)
   UNION ALL
(SELECT -1, st_code FROM states WHERE st_code < 'CA' ORDER BY st_code DESC LIMIT 1)

And I agree not a fan of unions, but having faced this problem before I still haven't found a better solution. I would love to know of a way to do it in a single query that doesn't use unions but as of yet I haven't found one.

NogDog

I can think of more convoluted ways that do not use a UNION, but I'm pretty sure they'd be considered worse (e.g. using a temp table to store a sorted result set, finding the item of interest in it, then getting the record before and after it based on the temp table's auto-incremented field :eek: ).

johanafm

Assuming you (rarely) have table modifications you could do a linked-list approach, storing previous and next ids in each row and join on those.

But any insert or delete would need to deal with this as well and I doubt you'd need the efficiency increase in retrievals to justify it.

tomhath

blehhh.. I hate unions! They are so unmathematical.

What's unmathematical about a UNION? Certainly no less so than a SELECT (which is a SUBSET) or a JOIN (which is an INTERSECTION).

Weedpacket

Well, relational algebra isn't set theory (one deals with relations, the other deals with sets, after all).

What's "unmathematical" is asking for the row before or after another one in the table. The principles of relational algebra (which is what relational databases are based on) mean that the rows of a table can be freely swapped about with each other without any change to the information stored in the table. There is no such thing as "the row before" another; all the ordering happens afterward.

Another suggested method to go with the ones above - assuming there aren't millions of states involved - would be to select all of them (if the column is indexed the table itself can be avoided), sort them however, then pick out whichever items are wanted and use those in a selection.

A relational solution would be to have a relation with attributes for state, preceding state, and following state. In other words, store the relevant data in the database.

tomhath

Well, relational algebra isn't set theory (one deals with relations, the other deals with sets, after all).

Not to be quarrelsome, but relational algebra is very closely related to set theory

http://www.cs.rochester.edu/~nelson/courses/csc_173/relations/algebra.html

"Operations of this algebra include the usual set operations (since relations are sets of tuples), and special operations defined for relations"

Weedpacket

Pretty much all of maths can be boiled down to set theory (and similarity is not enough for identity) 🙂. Thing is the "special operations defined for relations" include, in particular the operation of joining a pair of relations (it's not a set operation (viz intersection) on tuples because the intersection of two sets does not contain any elements that weren't in those sets, but the join of a pair of relations results in tuples that weren't in those relations).

But the status of a relation as a set of tuples once again makes the point that the order in which rows appear in a table is irrelevant to the information stored in the table.

tomhath

True statement, relational algebra contains additional operations (selection, projection, join, etc.) that aren't present in set theory. But the order of the rows in the table isn't relevant here, what he's looking at is the collated order of the result set.

Weedpacket

But you don't have a result set until after you have run a query to produce the results - so how can you define which records are to be selected based on the order in which they appear in something that doesn't exist yet?

(A result set is also a relation, and hence unordered - the point of a SELECT being to produce a new relation made up of a subset of tuples from another. Clauses like ORDER BY and LIMIT are conveniences added for efficiency in delivering results to the querying application and aren't part of the mathematical structure.)