SEO

#1 (**permalink**) 02-28-2008, 07:12 AM

Hallo,
I have a quite complex query:
I have a table

create tabel event
[...]
y INT
date TIMESTAMP
group INT
[...]

I'd like to know the value of y of the row which have the MAX(date) in
'group'.

something like that
SELECT y FROM event WHERE date is the max of (GROUP BY group)

How can I DO?
THnakyou in advance,
Andrea.

#2 (**permalink**) 02-28-2008, 07:12 AM

Hi!

If you use MySQL 4.1 upwards, you can do it with

select y from event where date = (select max(date) from event)

Be aware that this query could return more than one row if there are 2
events at the same time.

With MySQL 4.0 (and before), you would have to make 2 queries to get the
result.

Markus

#3 (**permalink**) 02-28-2008, 07:12 AM

Markus Popp wrote:
> Hi!
>
> If you use MySQL 4.1 upwards, you can do it with
>
select y from event where date = (select max(date) from event where
group=somegroup)

Is what you meant. And it will give a row per group.

Bart

#4 (**permalink**) 02-28-2008, 07:14 AM

_andrea.l wrote:
> Hallo,
> I have a quite complex query:
> I have a table
>
> create tabel event
> [...]
> y INT
> date TIMESTAMP
> group INT
> [...]
>
> I'd like to know the value of y of the row which have the MAX(date) in
> 'group'.
>
> something like that
> SELECT y FROM event WHERE date is the max of (GROUP BY group)
>
> How can I DO?
> THnakyou in advance,
> Andrea.
>
>
first you should rename your field group group1 ( because group is a
reserved name)
alter table event change 'group' 'group1' int(8);

You could not obtain "y" witch matches max(date) in one query:
select max(date),group1,y from event group by group1
-> returns a random y among group1.

the right way needs php.
PHP code:
<?
include('yourconnection.php');

//
$qu1 = "select max(date),group1 from event group by group1";
$res = mysql_query( $qu );

while( $row = mysql_fetch_array( $res )){

$qu2 = "select y,date,group1 from event where
date=".$row['max(date)']." and group1 = ".$row['group1'];

$res2 = mysql_query( $qu2 );

$row2 = mysql_fetch_array( $res2 );
//( or while $row2 = ... because $res2
// may return more than 1 result )

// print results:
echo "<pre>";
print_r( $row2 );
echo "</pre><hr>";
}
?>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Help for quite complex query.