This may have been covered in the past; but, marc.theaimsgroup isn't
responding so I can't run my normal search.
I'm trying to extract rows with max values within unique groups - NOT just
the max values. In very watered down terms:
Given:
CREATE TABLE test_table (
number tinyint(1) unsigned NOT NULL default '0',
value char(1) NOT NULL default '',
seq tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (number)
);
With data like this:
mysql> select * from test_table;
+--------+-------+-----+
| number | value | seq |
+--------+-------+-----+
| 1 | a | 1 |
| 2 | b | 1 |
| 3 | c | 1 |
| 4 | a | 2 |
| 5 | a | 3 |
| 6 | b | 2 |
+--------+-------+-----+
6 rows in set (0.00 sec)
I'd like to get rows, grouped by value, with a maximum seq. For
example; data that looks like this:
+--------+-------+-----+
| number | value | seq |
+--------+-------+-----+
| 5 | a | 3 |
| 3 | c | 1 |
| 6 | b | 2 |
+--------+-------+-----+
However, queries such as;
SELECT number, value, max(seq)
from test_table
group by value;
produce something like:
+--------+-------+----------+
| number | value | max(seq) |
+--------+-------+----------+
| 1 | a | 3 |
| 2 | b | 2 |
| 3 | c | 1 |
+--------+-------+----------+
Or, thinking I can force an order within the groups:
SELECT number, value, max(seq)
from test_table
group by value
order by seq DESC;
still produce:
+--------+-------+----------+
| number | value | max(seq) |
+--------+-------+----------+
| 1 | a | 3 |
| 2 | b | 2 |
| 3 | c | 1 |
+--------+-------+----------+
3 rows in set (0.00 sec)
But, of course, that's ordering the result set so the group by is beating
it to the punch.
I noted in the manual the quote "Don't use this feature if the columns you
omit from the GROUP BY part aren't unique in the group! You will get
unpredictable results"
I also tried using havings (something somewhat silly like having seq =
max(seq)) but, that doesn't produce anything for the same reasons I
presume as the order by.
So, that leaves two questions:
Is there a way to replicate using max() within a WHERE (ie, WHERE foo =
max(foo))
OR
Is there a way to order within a GROUP BY?
Ultimately, I'll be joining this data to other tables; so, it would be a
pain if I have to break this up into multiple queries.
---------------------------------------------------------------------
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
