Group by help?

[solinent]

I've got a table that looks like this:

 

+-----+--------+--------+----------+-------+---------+--------------+-------+------+-----+-----+------------+-----------+---------------------+
| car | week   | id     | username | time  | country | transmission | wrank | rank | ASM | TCS | tire_front | tire_back | updated             |
+-----+--------+--------+----------+-------+---------+--------------+-------+------+-----+-----+------------+-----------+---------------------+
|  43 | 104945 | 163164 | ommy     | 13704 | Japan   | MT           |     1 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104935 | 143194 | D-1      | 13599 | Japan   | MT           |     2 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104945 | 163165 | D-1      | 13589 | Japan   | MT           |     3 |    2 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104935 | 143195 | ommy     | 13561 | Japan   | MT           |     4 |    2 | OFF | 0   | N2         | N2        | 0000-00-00 00:00:00 | 
|  43 | 104942 | 157164 | ommy     | 13536 | Japan   | MT           |     5 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104933 | 139212 | D-1      | 13521 | Japan   | MT           |     6 |    1 | OFF | 0   | N2         | N2        | 0000-00-00 00:00:00 | 
|  43 | 104939 | 151164 | D-1      | 13508 | Japan   | MT           |     7 |    1 | OFF | 0   | N2         | N2        | 0000-00-00 00:00:00 | 
|  43 | 104942 | 157165 | D-1      | 13478 | Japan   | MT           |     8 |    2 | OFF | 0   | N2         | N2        | 0000-00-00 00:00:00 | 
|  43 | 104943 | 159164 | D-1      | 13477 | Japan   | MT           |     9 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104943 | 159165 | S_factry | 13440 | Japan   | MT           |    10 |    2 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104945 | 163166 | S_factry | 13436 | Japan   | MT           |    11 |    3 | OFF | 0   | N2         | N2        | 0000-00-00 00:00:00 | 
|  43 | 104938 | 149164 | D-1      | 13427 | Japan   | MT           |    12 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104936 | 145164 | D-1      | 13426 | Japan   | MT           |    13 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104940 | 153164 | S_factry | 13370 | Japan   | MT           |    14 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104942 | 157166 | S_factry | 13358 | Japan   | MT           |    15 |    3 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104934 | 141211 | D-1      | 13355 | Japan   | MT           |    16 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104937 | 147164 | S_factry | 13326 | Japan   | MT           |    17 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104941 | 155164 | D-1      | 13323 | Japan   | MT           |    18 |    1 | OFF | 0   | N2         | N2        | 0000-00-00 00:00:00 | 
|  43 | 104944 | 161164 | Redsupra | 13318 | Japan   | MT           |    19 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104933 | 139213 | Redsupra | 13299 | Japan   | MT           |    20 |    2 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
+-----+--------+--------+----------+-------+---------+--------------+-------+------+-----+-----+------------+-----------+---------------------+

 

(mysql output)

 

Basically, I need a function to pull out this result (with mySQL alone, I'm going to be pulling alot more from the db):

 

+-----+--------+--------+----------+-------+---------+--------------+-------+------+-----+-----+------------+-----------+---------------------+
| car | week   | id     | username | time  | country | transmission | wrank | rank | ASM | TCS | tire_front | tire_back | updated             |
+-----+--------+--------+----------+-------+---------+--------------+-------+------+-----+-----+------------+-----------+---------------------+
|  43 | 104945 | 163164 | ommy     | 13704 | Japan   | MT           |     1 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104935 | 143194 | D-1      | 13599 | Japan   | MT           |     2 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104943 | 159165 | S_factry | 13440 | Japan   | MT           |    10 |    2 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
|  43 | 104944 | 161164 | Redsupra | 13318 | Japan   | MT           |    19 |    1 | OFF | 0   | N1         | N1        | 0000-00-00 00:00:00 | 
+-----+--------+--------+----------+-------+---------+--------------+-------+------+-----+-----+------------+-----------+---------------------+

 

 

This is to eliminate doubles (in username column, and the one listed should be the one with the smallest wrank) when displaying these stats (I don't want to display twice).

 

Now, how do I do this?

 

The first command was brought to you by:

 SELECT * FROM gthd WHERE car=43 ORDER BY wrank LIMIT 20 

 

And the second command should go something like this?

 SELECT (list of columns...) MIN(wrank) FROM gthd WHERE car=43 GROUP BY username ORDER BY wrank LIMIT 20 

 

But that doesn't work, it produces a totally unrelated list of columns.

 

Please help

 

(just incase it makes anything clearer, wrank stands for "world rank"

[solinent]

Uh, I can't modify my orig post anymore, so sorry

 

Anyways, here's some progress I made, I'm able to get the rank numbers of the unique and wanted posters.  How can I cross reference this with a simple select statement?

 

SELECT MIN(wrank) AS worldrank FROM gthd WHERE car=43 AND reg != 'w' GROUP BY username ORDER BY worldrank ASC LIMIT 100;

 

(it makes it much easier when you type out messages like this, I find!  I just need to organise my thoughts.  I think theres an easy way to do this...)

[GingerRobot]

Are you sure what you tried didn't work? This should work:

 

SELECT *,min(wrank)as wrank FROM gthd GROUP BY username ORDER BY wrank ASC

[solinent]

Sort of.  I get columns, and the right username, but I need the row that corresponds with that wrank.  This way, I get two 'wrank':

 

car 	week 	id 	username 	time 	country 	transmission 	wrank 	rank 	ASM 	TCS 	tire_front 	tire_back 	updated 	reg 	wrank
43 	104924 	192973 	ommy 	9415 	j 	MT 	705 	21 	OFF 	0 	N1 	N1 	17:52 01/01 	j 	1
43 	104924 	192953 	D-1 	11629 	j 	MT 	189 	1 	OFF 	0 	N1 	N1 	01:02 30/12 	j 	2
43 	104924 	193009 	S_factry 	7589 	j 	MT 	1815 	57 	OFF 	0 	S1 	S1 	03:02 04/01 	j 	10
43 	104925 	198954 	Redsupra 	10223 	j 	MT 	441 	6 	OFF 	0 	N2 	N1 	02:10 11/01 	j 	19

 

Notice how wrank is right...and username, but there's two wrank's, and the first wrank corresonds the the column... so it's sort of a mixed result...

EDIT; I have to add stuff to make it work:

SELECT *,min(wrank)as wrank FROM gthd WHERE car=43 AND reg != 'w' GROUP BY username ORDER BY wrank ASC LIMIT 100;

 

car=43 is the most important, reg!='w' is just my mistake, and LIMIT 100 is so that I get 100 results.  The first 4 are above.  Thanks for your help!

 

EDIT: found this: somehow need to modify it to fit me?

http://www.thescripts.com/forum/thread76634.html

[solinent]

Help!

 

select g.*

from gthd g,

(select g2.car, g2.username, min(g2.wrank) as wrank from gthd g2 group

by g2.username ) as gg

where g.username = gg.username and g.wrank = gg.wrank AND gg.car=43 LIMIT 100

 

Goes on forever...

 

Ok, now it finished, but with the wrong results...

[solinent]

I got it to work with PHP in a couple minutes just now, very simple, but it does take 1.5 secs to execute... if mysql will be faster please tell me, if not, then I'll just use PHP.

 

My code for checking (if $skip=0 then it skips the recordset)

			foreach ($users as $user) {
				if ($db->recordSet[10]==$user) {
					$skip=1;
					break;
				} else {
					$skip=0;
				}
			}
			if ($u==100) {
				break;
			}

 

And then later on:

		if (!$skip) {
			$users[$u] = $db->recordSet[10];
			$u++;

 

Also, here is the page to see what output I want (if you still don't get it):

 

http://www.runeap.com/gthd/?m=4&cr=1&l=1

[GingerRobot]

Sort of.  I get columns, and the right username, but I need the row that corresponds with that wrank.  This way, I get two 'wrank':

 

car 	week 	id 	username 	time 	country 	transmission 	wrank 	rank 	ASM 	TCS 	tire_front 	tire_back 	updated 	reg 	wrank
43 	104924 	192973 	ommy 	9415 	j 	MT 	705 	21 	OFF 	0 	N1 	N1 	17:52 01/01 	j 	1
43 	104924 	192953 	D-1 	11629 	j 	MT 	189 	1 	OFF 	0 	N1 	N1 	01:02 30/12 	j 	2
43 	104924 	193009 	S_factry 	7589 	j 	MT 	1815 	57 	OFF 	0 	S1 	S1 	03:02 04/01 	j 	10
43 	104925 	198954 	Redsupra 	10223 	j 	MT 	441 	6 	OFF 	0 	N2 	N1 	02:10 11/01 	j 	19

 

Notice how wrank is right...and username, but there's two wrank's, and the first wrank corresonds the the column... so it's sort of a mixed result...

 

So just to be clear, its basically working, but the problem is that you have two wranks displayed? If so, i still think this is your best bet. I'd say you have two options - either list the exact fields you want rather than using *, or if you are outputing your results in php, then just only show one of the wranks.

[solinent]

No no no, all the fields are wrong.  So the most important field, their time, is wrong.  It corresponds to a totally different wrank (the first one) rather than the one I displayed.

[Wildbug]

SELECT a.*
FROM
gthd AS a
JOIN (SELECT username,MIN(wrank) AS minwrank FROM gthd GROUP BY username) AS b
ON a.username=b.username AND a.wrank=b.minwrank;

 

That doesn't work?

[solinent]

Let me try, one second.  I have gotten the load times under .7 seconds by doing "OPTIMIZE".

 

... it is taking quite a long time to load, I hope I didn't break anything...  This happend last time when I thought I had a good query.  IS somehow the query circular that causes nothing to load?

 

Here's what I modified

SELECT a.*
FROM
gthd AS a
JOIN (SELECT username,MIN(wrank) AS minwrank FROM gthd WHERE car=43 AND reg!= 'w' GROUP BY username) AS b
ON a.username=b.username AND a.wrank=b.minwrank;

 

Testing in phpMyAdmin

[Wildbug]

(I don't see reg in the table in the original post.)

 

Try putting your WHERE constraints in the outer query as well as the inner.

 

SELECT a.*
FROM
gthd AS a
JOIN (SELECT username,MIN(wrank) AS minwrank FROM gthd WHERE car=43 AND reg!= 'w' GROUP BY username) AS b
ON a.username=b.username AND a.wrank=b.minwrank
WHERE car=43 AND reg!= 'w' ;