How to select all rows with a unique field, and get all fields in that row in the result set in MySQL?

Question

I have a table say something like this:

column_a  column_b
   foo        1
   bar        1
   baz        2

I need to get this in my result set:

    foo 1
    baz 2

Thus, I need to select rows where the field column_b is distinct. I tried coming up with ways to do this by using a subquery with select distinct but no dice. I'm still kind of noobish at SQL.

Answer 1

Something like this should do it - does it matter which column_a you get for each distinct value of column_b?

select column_a,column_b from yourtable group by column_b

Answer 2

You may want to try the following (using a datetime field, further to comments above):

SELECT   (
            SELECT   column_a 
            FROM     foo_table f2 
            WHERE    f2.column_b = f1.column_b 
            ORDER BY f2.date DESC
            LIMIT    1
         ) column_a,
         f1.column_b
FROM     foo_table f1
GROUP BY f1.column_b;

Test case:

CREATE TABLE foo_table (column_a varchar(10), column_b int, date datetime);

INSERT INTO foo_table VALUES ('foo', 1, '2010-06-03 00:00:00');
INSERT INTO foo_table VALUES ('bar', 1, '2010-06-02 00:00:00');
INSERT INTO foo_table VALUES ('baz', 2, '2010-06-01 00:00:00');

Result:

+----------+----------+
| column_a | column_b |
+----------+----------+
| foo      |        1 |
| baz      |        2 |
+----------+----------+
2 rows in set (0.04 sec)