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]

*** Bob Bedford wrote/escribió (Mon, 23 May 2005 09:44:28 +0200):

id datefrom dateto

In my case, I'd like to show the last date for any ID.

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]

"Alvaro G Vicario" <al******************@telecomputeronline.com> a écrit
dans le message de news: 13*****************************@40tude.net...

*** Bob Bedford wrote/escribió (Mon, 23 May 2005 09:44:28 +0200):

id datefrom dateto

In my case, I'd like to show the last date for any ID.

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

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]

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