By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,409 Members | 1,604 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,409 IT Pros & Developers. It's quick & easy.

Mysql question

P: n/a
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
Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
*** 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
--
Jul 17 '05 #2

P: n/a

"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 ?
Jul 17 '05 #3

P: n/a
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
Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.