[SOLVED] best query to get results I want

[funkyres]

Here's the table and data I'm interested in:

mysql> describe hexrecords;
+------------+-----------------------+------+-----+---------+----------------+
| Field      | Type                  | Null | Key | Default | Extra          |
+------------+-----------------------+------+-----+---------+----------------+
| id         | mediumint( unsigned | NO   | PRI | NULL    | auto_increment | 
| herprecord | mediumint( unsigned | NO   | UNI |         |                | 
| herpid     | tinyint(3) unsigned   | NO   |     |         |                | 
| hexid      | smallint(5) unsigned  | NO   |     |         |                | 
| rec_date   | date                  | NO   |     |         |                | 
| museum     | tinyint(4)            | NO   |     | 0       |                | 
+------------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> select hexid,rec_date,museum from hexrecords where herpid=3;
+-------+------------+--------+
| hexid | rec_date   | museum |
+-------+------------+--------+
|    76 | 2004-06-18 |      0 | 
|    23 | 2005-07-17 |      0 | 
|    56 | 1056-03-12 |      1 | 
|    23 | 1976-04-05 |      1 | 
|    56 | 2003-07-21 |      0 | 
|    56 | 2008-05-12 |      0 | 
|    76 | 2003-08-18 |      0 | 
|    23 | 1989-07-04 |      1 | 
|    76 | 2001-06-16 |      0 | 
+-------+------------+--------+
9 rows in set (0.00 sec)

 

What I want is a query that will give me the hexid with the most recent date.

IE a query that would spit out

 

+-------+------------+--------+
| hexid | rec_date   | museum |
+-------+------------+--------+
|    76 | 2004-06-18 |      0 |
|    56 | 2008-05-12 |      0 |
|    23 | 2005-07-17 |      0 |

 

Any suggestions on how to write a query that results in one row per unique hexid that corresponds to the most recent date for that hexid?

 

I'd rather not have to do separate queries for each hexid, as with the real data there could be several hundred of them per herpid - and I'd also rather not suck them all into my php script and select the most current date for a given hexid there. If there's a MySQL select that does it, I'd be very grateful. Distinct would work with date sorting if there was a way to specify that the hexid needs to be distinct regardless of other fields, but I don't know how to do that.

[funkyres]

select hexid,rec_date,museum from hexrecords where herpid=3 ORDER BY rec_date desc;

 

gets me closer - if there was a way I could have it not show rows that start with a hexid already used.

[funkyres]

mysql> select distinct(hexid),rec_date,museum from hexrecords where herpid=3 GROUP BY hexid ORDER BY rec_date desc;
+-------+------------+--------+
| hexid | rec_date   | museum |
+-------+------------+--------+
|    23 | 2005-07-17 |      0 | 
|    76 | 2004-06-18 |      0 | 
|    56 | 1056-03-12 |      1 | 
+-------+------------+--------+
3 rows in set (0.00 sec)

 

gets me 3 rows of data with unique hexid - but doesn't do it based upon the newest date.

I guess it's closer ...

 

Oh - I'm running CentOS build - mysql-5.0.45-7.el5 (server and client) if that matters.

[funkyres]

mysql> select distinct(hexid),rec_date,museum from (select hexid,rec_date,museum from hexrecords where herpid=3 ORDER BY rec_date desc) as tbl GROUP BY hexid;
+-------+------------+--------+
| hexid | rec_date   | museum |
+-------+------------+--------+
|    23 | 2005-07-17 |      0 | 
|    56 | 2008-05-12 |      0 | 
|    76 | 2004-06-18 |      0 | 
+-------+------------+--------+
3 rows in set (0.00 sec)

 

seems to do the trick!