I am running a stats page for our Genome@Home team, and I'm trying to get a
simple SELECT that will get the data that I need...
Ok, I have a table setup like this:
Genomestats
-----------------------------------
username varchar(255)
curDate datetime
wucount bigint
genecount bigint
updates int
rank int
teamnumber bigint
A sample record from the table may look like this:
|DP | 2001-08-24 16:00:00 | 25786 | 1588 | 1 | 24 |
983928120|
My SELECT looks like this (note that the dates are dynamically generated):
SELECT * FROM genomestats WHERE curDate IN ('$curDate', '$lastUpdate',
'$lastDay') order by wucount desc, rank desc;
Where $curDate is the most recent data update, $lastUpdate is the update
from 3 hours ago, and $lastDay is the update from 24 hours ago. When I
execute the SQL, and print the resulting recordset to my log file, I get
data like this:
junior || 2001-09-14 06:30:00 || 51819 || 3019 || 156 || 11 || 983928120
junior || 2001-09-14 03:30:00 || 51743 || 3014 || 155 || 11 || 983928120
junior || 2001-09-13 06:30:00 || 51480 || 2997 || 149 || 11 || 983928120
IronBits || 2001-09-14 06:30:00 || 50787 || 3021 || 156 || 12 || 983928120
IronBits || 2001-09-14 03:30:00 || 50712 || 3016 || 155 || 12 || 983928120
Zoso || 2001-09-14 06:30:00 || 50549 || 2917 || 156 || 13 || 983928120 Zoso
|| 2001-09-14 03:30:00 || 50505 || 2914 || 155 || 13 || 983928120 Zoso ||
2001-09-13 06:30:00 || 50188 || 2893 || 149 || 12 || 983928120 IronBits ||
2001-09-13 06:30:00 || 50181 || 2982 || 149 || 13 || 983928120
Now, the problem is that the user IronBits had a wucount at somepoint (in
the last 24 hours here) that was less than Zoso's wucount. Basically, this
means that IronBits passed Zoso sometime in the past 24 hours. I need to
have all of the data grouped by the username, but if I add in group by
username, then I only get one set of records, not 3, as shown above. Here
is what I want:
junior || 2001-09-14 06:30:00 || 51819 || 3019 || 156 || 11 || 983928120
junior || 2001-09-14 03:30:00 || 51743 || 3014 || 155 || 11 || 983928120
junior || 2001-09-13 06:30:00 || 51480 || 2997 || 149 || 11 || 983928120
IronBits || 2001-09-14 06:30:00 || 50787 || 3021 || 156 || 12 || 983928120
IronBits || 2001-09-14 03:30:00 || 50712 || 3016 || 155 || 12 || 983928120
IronBits || 2001-09-13 06:30:00 || 50181 || 2982 || 149 || 13 || 983928120
Zoso || 2001-09-14 06:30:00 || 50549 || 2917 || 156 || 13 || 983928120 Zoso
|| 2001-09-14 03:30:00 || 50505 || 2914 || 155 || 13 || 983928120 Zoso ||
2001-09-13 06:30:00 || 50188 || 2893 || 149 || 12 || 983928120
But I can't think of a good, simple way to do this. Anyone have any ideas?
-------------------------
Glendon Solsberry
Internet Programmer
Tricon Global Restaurants
tel. (502) 874-6736
fax (502) 874-8818
---------------------------------------------------------------------
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
