[thelist] An sql problem

rudy r937 at interlog.com
Fri May 4 19:53:03 CDT 2001


>What about this to select the previous
>
>SELECT id
>FROM table
>WHERE ref < [thisone.ref]
>ORDER BY ref DESC
>
>where [thisone.ref] is the ref of the one you are currently viewing. Then
>make sure that the query is set up to retreive only one row.

hi joshua

brilliant, and twice as good as my suggestion, 'cause it's only one query

(i gotta stop drinkin)

your approach is maximally efficient, too

in mysql the restriction to one row returned is it done *in the database*
as opposed to returning a result set which the server script would just
take the first record of (which would be another solution if the database
didn't have some way of limiting the number of rows)

also, it may not even require a sort!!!  if the mysql optimizer is smart
enough, it should be able to utilize the index of ref, in effect just read
the next entry in the index

brilliant

in mysql, philippe, you restrict the number of rows returned using the
LIMIT keyword

  SELECT id
  FROM table
  WHERE ref < [thisone.ref]
  ORDER BY ref DESC
  LIMIT 1


good one, josh


rudy











More information about the thelist mailing list