Mysql question

[Bob Bedford]

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

[Alvaro G Vicario]

Re: Mysql question

*** Bob Bedford wrote/escribió (Mon, 23 May 2005 09:44:28 +0200):[color=blue]
> id datefrom dateto
>
> In my case, I'd like to show the last date for any ID.[/color]

Then you don't need datefrom and dateto but MAX(datefrom) and MAX(dateto)


--
-- Álvaro G. Vicario - Burgos, Spain
-- http://bits.demogracia.com - Mi sitio sobre programación web
-- Don't e-mail me your questions, post them to the group
--

[Bob Bedford]

Re: Mysql question


"Alvaro G Vicario" <alvaro_QUITAR_ REMOVE@telecomp uteronline.com> a écrit
dans le message de news: 13bwff5w9iibj.7 kybkobyuwzy$.dl g@40tude.net...[color=blue]
> *** Bob Bedford wrote/escribió (Mon, 23 May 2005 09:44:28 +0200):[color=green]
>> id datefrom dateto
>>
>> In my case, I'd like to show the last date for any ID.[/color]
>
> Then you don't need datefrom and dateto but MAX(datefrom) and MAX(dateto)[/color]

MAX needs to group by, and having 20 fields in my query, should I group by
all fields ? Also this field may be empty sometimes.

Isn't there any other solution ?

[jerry gitomer]

Re: Mysql question

Bob Bedford wrote:[color=blue]
> 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
>
>[/color]
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