[snip]
2. The date of max disksize and the date of min disksize...in one query...if
that's possible.
[/snip]
mysql> select RunDate,
-> MAX(IF(ClientName = 'ABC INC', DiskSize, '')) AS ABC,
-> MAX(IF(ClientName = 'Eat at Joe', DiskSize, '')) AS Joe,
-> MAX(IF(ClientName = 'The Hop', DiskSize, '')) AS Hop,
-> MAX(IF(ClientName = 'Razoo', DiskSize, '')) AS Razoo
-> from tblBAR
-> group by RunDate
-> order by DiskSize DESC;
+------------+------+------+------+-------+
| RunDate | ABC | Joe | Hop | Razoo |
+------------+------+------+------+-------+
| 2002-07-12 | 3.0 | | | |
| 2002-07-04 | | | | 2.6 |
| 2002-07-13 | 1.8 | 2.0 | | |
| 2002-07-11 | 1.2 | 1.8 | | |
| 2002-07-17 | | | 1.1 | 1.1 |
| 2002-07-14 | | 0.8 | | |
| 2002-07-08 | | | 0.6 | |
+------------+------+------+------+-------+
7 rows in set (0.00 sec)
This requires that you know each client. If you look at column ABC and go
down, the first value is the MAX disk size, go left to find the date this
occured. Look at column Hop and go down to the first value, that is the MAX,
go left to find the date this occured.
mysql> select RunDate,
-> MAX(IF(ClientName = 'ABC INC', DiskSize, '')) AS ABC,
-> MAX(IF(ClientName = 'Eat at Joe', DiskSize, '')) AS Joe,
-> MAX(IF(ClientName = 'The Hop', DiskSize, '')) AS Hop,
-> MAX(IF(ClientName = 'Razoo', DiskSize, '')) AS Razoo
-> from tblBAR
-> group by RunDate
-> order by DiskSize;
+------------+------+------+------+-------+
| RunDate | ABC | Joe | Hop | Razoo |
+------------+------+------+------+-------+
| 2002-07-08 | | | 0.6 | |
| 2002-07-14 | | 0.8 | | |
| 2002-07-17 | | | 1.1 | 1.1 |
| 2002-07-11 | 1.2 | 1.8 | | |
| 2002-07-13 | 1.8 | 2.0 | | |
| 2002-07-04 | | | | 2.6 |
| 2002-07-12 | 3.0 | | | |
+------------+------+------+------+-------+
7 rows in set (0.00 sec)
This is still calcing MAX, but since there is ORDER BY DiskSize you can take
the DESC (descending) attribute out and reading the table is the same. The
first value down the column is the MINIMUM, go left to find the date this
occured.
HTH!
Jay
---------------------------------------------------------------------
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
