Try it and see. What I think you want is to rank your usernames according
to who has the highest wucount and rank and then show the individual records
in descending order of wucount/rank but grouping records by username. This
differs from the suggestion of simply adding username at the front of your
order by in that in that scenario, the records will be sorted alphabetically
by username. If you want to group by username, then you must determine in
what order usernames will print. The solution I have outlined will order
the usernames according to who has the highest (max) wucount and rank.
Within each username group, the individual records will follow your original
order of wucount desc, and rank desc. The only potential problem with this
is that the max(wucount) and the max(rank) for each username may not occur
within the same individual record. You should think about the ramifications
of this and decide if.
Like I said, give it a try... what have you got to lose?
-----Original Message-----
From: Solsberry, Glendon [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 19, 2001 11:35 AM
To: [EMAIL PROTECTED]
Subject: RE: Simple SELECT query (or so I thought)
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
---------------------------------------------------------------------
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
