Hello,

I've a table in wich I've a few columns containing dates:

create table dates(
Iddates integer unsigned not null auto_increment,
IdGroup integer unsigned NOT NULL,
D1 date,
D2 date,
D3 date,
D4 date,
PRIMARY KEY (`Iddates`)
);

For each record I may have one or more values in fields D1 to D4 , so some
may be empty (null) and some not.
For every group, I may have 0 or more record like

1,50,'2007-01-10','','',''
1,50,'2007-01-15','','',''
1,50,'2007-01-20','2007-02-20','',''

Now my question is how to get the max date from all records where idgroup =
iditem on an other table, in one step ?

something like:
select max(D1,D2,D3,D4) from dates inner join items on dates.idgroup =
items.mode where items.mode <> 0//inner join only return existing values.

Thanks for ing and happy new year !!!

Bob Bedford wrote:
> Hello,
>
> I've a table in wich I've a few columns containing dates:
>
> create table dates(
> Iddates integer unsigned not null auto_increment,
> IdGroup integer unsigned NOT NULL,
> D1 date,
> D2 date,
> D3 date,
> D4 date,
> PRIMARY KEY (`Iddates`)
> );
>
> For each record I may have one or more values in fields D1 to D4 , so
> some may be empty (null) and some not.
> For every group, I may have 0 or more record like
>
> 1,50,'2007-01-10','','',''
> 1,50,'2007-01-15','','',''
> 1,50,'2007-01-20','2007-02-20','',''
>
> Now my question is how to get the max date from all records where
> idgroup = iditem on an other table, in one step ?
>
> something like:
> select max(D1,D2,D3,D4) from dates inner join items on dates.idgroup =
> items.mode where items.mode <> 0//inner join only return existing
> values.
> Thanks for ing and happy new year !!!

I think some sample input and expected output data would be useful here

> I think some sample input and expected output data would be useful here

If found the solution:
select max(greatest(C1,C2,C3,C4)) from dates...

In fact giving those datas:
> 1,50,'2007-01-10','','',''
> 1,50,'2007-01-15','','',''
> 1,50,'2007-01-20','2007-02-20','',''

I wanted to know the biggest values of all fields C1,C2,C3 and C4 for all
records.
In my example, it should return 2007-02-20

Bob

On Fri, 28 Dec 2007 17:22:53 +0100, Bob Bedford wrote:
> Hello,
>
> I've a table in wich I've a few columns containing dates:
>
> create table dates(
> Iddates integer unsigned not null auto_increment,
> IdGroup integer unsigned NOT NULL,
> D1 date,
> D2 date,
> D3 date,
> D4 date,
> PRIMARY KEY (`Iddates`)
> );
>
> For each record I may have one or more values in fields D1 to D4 , so some
> may be empty (null) and some not.
> For every group, I may have 0 or more record like
>
> 1,50,'2007-01-10','','',''
> 1,50,'2007-01-15','','',''
> 1,50,'2007-01-20','2007-02-20','',''
>
> Now my question is how to get the max date from all records where idgroup =
> iditem on an other table, in one step ?
>
> something like:
> select max(D1,D2,D3,D4) from dates inner join items on dates.idgroup =
> items.mode where items.mode <> 0//inner join only return existing values.
>
> Thanks for ing and happy new year !!!
>

select max(d1), max(d2), max(d3), max(d4) ... group by iddates, idgroup;

might be close to what you want. I *think* (I haven't tested) that
you'll end up with the highest in each column for those. If you're
looking for getting just a single date back from the above data, you'll
probably be better off changing your design and/or loading a scratch
table with only one date column, but up to four records for each you
currently have.


--
Judging by this particular thread, many people in this group spent their
school years taking illogical, pointless orders from morons and having
their will to live systematically crushed. And people say school doesn't
prepare kids for the real world. -- Rayner, in the Monastery

Bob Bedford wrote:
>> I think some sample input and expected output data would be useful
>> here
>
> If found the solution:
> select max(greatest(C1,C2,C3,C4)) from dates...
>
> In fact giving those datas:
>> 1,50,'2007-01-10','','',''
>> 1,50,'2007-01-15','','',''
>> 1,50,'2007-01-20','2007-02-20','',''
>
> I wanted to know the biggest values of all fields C1,C2,C3 and C4 for
> all records.
> In my example, it should return 2007-02-20
>
> Bob

you need a slight modification of the strawberry query (and a primary key)