If you change you order by to
order by username, wucount desc, rank desc
Won't this group it the way you want it ??
Terje K
> -----Opprinnelig melding-----
> Fra: Solsberry, Glendon [mailto:[EMAIL PROTECTED]]
> Sendt: 19. september 2001 15:29
> Til: '[EMAIL PROTECTED]'
> Emne: 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
