asked on

GROUP BY and alternate rows

I'm trying to eliminate duplicate rows of a particular column in a select query. Group by allows me to do that but it is returning the first row inserted instead, I need the last row inserted.

Here's a sample table:

+------+--------+
| num | letter |
+------+--------+
| 1 | A |
| 2 | A |
| 3 | B |
| 4 | B |
+------+--------+

Here's a sample query:

mysql> select num, letter from sample GROUP BY letter ORDER BY num DESC;
+------+--------+
| num | letter |
+------+--------+
| 3 | B |
| 1 | A |
+------+--------+

I'm looking to get num 2 & 4, not num 1 & 3.

I'm running MySQL 4.0

ldbkutty

SELECT MAX(num), letter
FROM sample
GROUP BY letter
ORDER BY num DESC

phasevar

ASKER

Hmmm.. Is there a way to accomplish that if I'm already using ORDER BY on a different column? Such as this:

+------+--------+------+
| num | letter | str |
+------+--------+------+
| 1 | A | zzz |
| 2 | A | yyy |
| 3 | B | xxx |
| 4 | B | www |
+------+--------+------+

mysql> SELECT num, letter, str FROM sample GROUP BY letter ORDER BY str DESC;
+------+--------+------+
| num | letter | str |
+------+--------+------+
| 1 | A | zzz |
| 3 | B | xxx |
+------+--------+------+

As above, I'm looking for num to be 2 & 4.

as2003

As you're using MySQL 4.0 there isn't going to be an elegant solution. Have a look at this: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html (and consider upgrading to v4.1 or greater!)

ASKER CERTIFIED SOLUTION

as2003

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tromm

yes. The max-function is not influenced by order by.