On Thursday 27 Dec 2001 23:31, Billy Reed wrote:
> I am new to MySQL and am trying to convert :
>
> SELECT SUM(DistKm) AS Distance FROM (SELECT TOP 5 DistKm FROM XCLeague
> WHERE LoginID='billyreed' AND HGPG='PG' ORDER BY DistKm DESC)
>
> Can anyone suggest how this is done in MySQL. It doesnt seem to like the
> TOP 5 syntax.
I am not sure sub-selects are actually supported in MySQL, but in the SQL
dialect of MySQL IF sub-selects were supported, it would be something like:
SELECT SUM(DistKm) AS Distance FROM (SELECT DistKm FROM XCLeague
WHERE LoginID='billyreed' AND HGPG='PG' ORDER BY DistKm DESC LIMIT 5).
Without sub-selects, you will probably want something like:
CREATE TEMPORARY TABLE IF NOT EXISTS TempTable Type = Heap
(
DistKm integer unsigned
)
SELECT DistKm
FROM XCLeague
WHERE LoginID = 'billyreed' AND
HGPG = 'PG'
ORDER BY DistKm DESC
LIMIT 5;
SELECT Sum(DistKm)
AS Distance
FROM TempTable;
DROP TABLE TempTable;
Make sure the CREATE TABLE above returns '1' before proceeding with the rest,
as there could be race-condition where the new table gets created by another
user before the old TempTable gets dropped below. If CREATE fails, try until
it works, or it times out... It could be that TEMPORARY tables only exist in
the scope of current connection (they do on PostgreSQL, I'm not sure about
MySQL as I never used this feature), but then you could potentially run into
problems with persistent connections in Perl and/or PHP, so you probably want
to drop it just to make sure.
Regards.
Gordan
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
