Database Reference
In-Depth Information
+---------------+
| @min_avg_time |
+---------------+
| 23.6667 |
+---------------+
That's about right. We had a value of 23 seconds before, but that's because we rounded it
with
TIME_FORMAT()
. This ismore accurate. Let's use that variable now to do a com-
parisonusing
SIGN()
in the
WHERE
clause. Enter this on your server:
SELECT CONCAT(name_first, SPACE(1), name_last)
AS 'Birdwatcher',
common_name AS 'Bird',
ROUND(@min_avg_time - TIME_TO_SEC( TIMEDIFF(id_end, id_start) ) )
AS 'Seconds Less than Average'
FROM bird_identification_tests
JOIN humans USING(human_id)
JOIN rookery.birds USING(bird_id)
WHERE SIGN( TIME_TO_SEC( TIMEDIFF(id_end, id_start) -
@min_avg_time)) = -1;
+-------------------+----------------------+---------------------------+
| Birdwatcher | Bird Identified | Seconds Less than
Average |
+-------------------+----------------------+---------------------------+
| Lexi Hollar | Blue Duck |
3 |
| Lexi Hollar | Trinidad Piping-Guan |
13 |
| Geoffrey Dyer | Javan Plover |
15 |
| Katerina Smirnova | Blue Duck |
2 |
| Anahit Vanetsyan | Great Crested Grebe |
4 |
+-------------------+----------------------+---------------------------+
The use of
SIGN()
in the
WHERE
clause selects rows in which the member took less than
the average time. That's a function that would be difficult to duplicate in MySQL by any
othermethod.
