Database Reference
In-Depth Information
• “Pick a card, any card” magic tricks that begin with a full deck of cards each time
To pick more than one item, change the
LIMIT
argument. For example, to draw five
winning entries at random from a table named
drawing
that contains contest entries,
use
RAND()
in combination with
LIMIT
:
SELECT
*
FROM
drawing
ORDER
BY
RAND
()
LIMIT
5
;
A special case occurs when you pick a single row from a table that you know contains
a column with values in the range from 1 to
n
in unbroken sequence. Under these
circumstances, it's possible to avoid performing an
ORDER
BY
operation on the entire
table. Pick a random number in that range and select the matching row:
SET
@
id
=
FLOOR
(
RAND
()
*
n
)
+
1
;
SELECT
...
FROM
tbl_name
WHERE
id
=
@
id
;
This is much quicker than
ORDER
BY
RAND()
LIMIT
1
as the table size increases.
15.9. Calculating Successive-Row Differences
Problem
A table contains successive cumulative values in its rows, and you want to compute the
differences between pairs of successive rows.
Solution
Use a self-join that matches pairs of adjacent rows and calculates the differences between
members of each pair.
Discussion
Self-joins are useful when you have a set of absolute (or cumulative) values that you
want to convert to relative values representing the differences between successive pairs
of rows. For example, if you take an automobile trip and write down the total miles
traveled at each stopping point, you can compute the difference between successive
points to determine the distance from one stop to the next. Here is such a table that
shows the stops for a trip from San Antonio, Texas to Madison, Wisconsin. Each row
shows the total miles driven as of each stop:
mysql>
SELECT seq, city, miles FROM trip_log ORDER BY seq;
+-----+------------------+-------+
| seq | city | miles |
+-----+------------------+-------+
| 1 | San Antonio, TX | 0 |
| 2 | Dallas, TX | 263 |
| 3 | Benton, AR | 566 |
| 4 | Memphis, TN | 745 |
