Correct. I need the data sorted by the wucount first, but still have the
data grouped together by the username. I don't quite follow the reasoning
behind a temp table. Won't I still have the problems of the sorting, or can
you explain to me how this will solve that problem?
Glen
-----Original Message-----
From: Will French [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 19, 2001 9:59 AM
To: Solsberry, Glendon; [EMAIL PROTECTED]
Subject: RE: Simple SELECT query (or so I thought)
I am not possitive that I completely follow what you are trying to do...
but, here goes:
What if you create a temporary table first:
CREATE TEMPORARY TABLE tu (
username varchar(255),
maxwucount bigint,
maxrank int
) SELECT username, max(wucount) as maxwucount, max(rank) as maxrank
FROM genomestats
WHERE curDate IN ('$curDate', '$lastUpdate', '$lastDay')
GROUP BY username;
Then:
SELECT genomestats.*
FROM genomestats INNER JOIN tu ON genomestats.username = tu.username
WHERE genomestats.curDate IN ('$curDate', '$lastUpdate', '$lastDay') ORDER
BY tu.maxwucount desc, tu.maxrank desc, tu.username,
genomestats.wucount desc, genomestats.rank desc
I've seen the other suggestions telling you to just add the username to your
order by but if I understand your situation this would not work because it
would not satisfy your wish to have the list sorted with the highest
wucount/rank first... right?
-----Original Message-----
From: Solsberry, Glendon [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 19, 2001 9:29 AM
To: '[EMAIL PROTECTED]'
Subject: Simple SELECT query (or so I thought)
Can anybody help me out with this??? This would make life so much easier
for my poor stats package. Anyone?
-----Original Message-----
From: Solsberry, Glendon
Sent: Friday, September 14, 2001 10:17 AM
To: MySQL
Subject: Simple SELECT query (or so I thought)
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
