Show records from the most recent date only.

[suttercain]

Hi everyone,

 

Please bear with me as I try and write this in a concise manner. I have three tables:

 

TABLE A

| group | name  |

----------------------

| 1        | Yellow |

| 2        | Yellow |

| 3        | Blue    |

----------------------

 

TABLE B

| group | code | date_created |

---------------------------------------

| 1        | j7uw | 2009-10-10

| 1        | op0i  | 2009-10-10

| 1        | hw7i | 2009-10-09

| 1        | usp1 | 2009-10-10

| 2        | eiu2  | 2010-07-21

| 2        | y9oi  | 2009-12-25

| 2        | j7uw | 2009-12-25

---------------------------------------

 

TABLE C

| code | size |

------------------

| j7uw | Large

| op0i  | Large

| hw7i | Medium

| usp1 | Small

| eiu2  | Medium

| y9oi  | XLarge

| j7uw | Large

------------------

 

What I'm doing is an INNER JOIN on these tables:

SELECT *
FROM table_a
INNER JOIN table_b (table_a.group = table_b.group)
INNER JOIN table_c (table_b.code = table_c.code)
WHERE table_b.group = 1

 

With the above query I'll get this expected output:

 

| 1        | j7uw | 2009-10-10 | Yellow | Large

| 1        | op0i  | 2009-10-10 | Yellow | Large

| 1        | hw7i | 2009-10-09 | Yellow | Medium

| 1        | usp1 | 2009-10-10 | Yellow | Small

 

My desired results however are only the MOST RECENT date_created from the date column based on the selected group. Meaning, I'd like to get these results instead:

 

| 1        | j7uw | 2009-10-10 | Yellow | Large

| 1        | op0i  | 2009-10-10 | Yellow | Large

| 1        | usp1 | 2009-10-10 | Yellow | Small

 

Because the other date was October, 9 2009 and "newer" date(s) have occured within that group, that record(s) will be omitted.

 

So if tomorrow a new record is added with GROUP 1 and a date of 2009-10-11, only that newest record will appear. There may also be multiple records with the same date and group and I need them all to display.

 

Any ideas?

 

Thanks ahead of time.

 

-SC

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html