Posted by jerry gitomer on 05/23/05 16:38

Bob Bedford wrote:
> I haven't access to Mysql NG, so let me ask here.
>
> Query:
>
> select distinct a,b from a left join b on a.id = b.id
>
> table b:
> id datefrom dateto
> 1 2005-01-01 2005-01-15
> 2 2005-01-02 2005-01-16
> 1 2005-01-17 2005-01-31
>
> In my case, I'd like to show the last date for any ID.
> So the result:
> 1 2005-01-31
> 2 2005-01-16
> 3 -
>
> How to do so ? In my case, I get two rows for ID 1
>
> Must I run 2 queries ? It's really hard to create a query with GroupBy,
> as the real query takes 20 fields
>
>
Bob,

If you are using a version of MySQL that allows subqueries you
can do a sub query for the max date and then only select those
rows that have an equal date in the main query.

HTH
Jerry

[Back to original message]