SQL : problem with MAX

[jpeg]

I've got a table 'tmp' with these columns :

id,day,hour,value

I want to get the 'hour' when max of 'value' is reached for each 'day'.

the query :
"SELECT id,day,hour,max(value) from tmp group by day"
doesn't return the wanted rows.

Do you have any idea to get what I want ?

[nicc777]

See if this helps: http://www.tol.oulu.fi/~jlappala/jth/h3/Groupby.pdf

Cheers

[hi_psandeep]

hi,

You better try this query,

SELECT id,day,hour,value from tmp where hours=(select max(hour) from tmp group by day);

Bye

Sandeep

[tallpeak]

You might be able to merge the fields, then split them back out, by converting to text.

MS SQL Server, assuming value is an int

SELECT day,max(right(' '+convert(varchar,value),10)+right(' '+convert(varchar,id),10)+convert(varchar,hour)) mixedresult
FROM tmp

...then split mixedresult into three fields using some sort of substr or mid or unpack function in the client application.

If the values to be converted to text are not integers, you would have to use round(value*100,0) for example to convert to integer, then convert back in the client app.

[nicc777]

Here goes:

      1	#!/usr/bin/perl
      2	      
      3	use strict;
      4	use DBI;
      5	      
      6	my $dbusername = "root";
      7	my $dbpassword = "";
      8	my $dbhost = ""; 
      9	my $dbdatabasename = "test";
     10	my $dbh1;
     11	
     12	my $db = "dbi:mysql:" . $dbdatabasename;
     13	my $dbh1 = DBI->connect( $db, $dbusername, $dbpassword ) or die("DB ERROR : $!");
     14
     15	my $sql1 = "SELECT `id`,`day`,`hour`,`value` FROM `test`";
     16	my $sth1 = $dbh1->prepare( $sql1 );
     17	my %max = ();
     18	if( $sth1->execute() ) {
     19
     20		while( ( my $id,my $day,my $hour,my $value ) = $sth1->fetchrow_array() ) {
     21		     
     22			if( $max{ $day } ) {
     23			     
     24				if( $value > $max{ $day } ) { $max{ $day } = $value; }
     25			     
     26			} else {
     27
     28				$max{ $day } = $value;
     29
     30			}
     31
     32		}
     33	     
     34	} else {
     35	     
     36		exit;
     37	
     38	}
     39	
     40	if ( $dbh1 ) { $dbh1->disconnect(); }
     41	
     42	foreach my $day ( sort keys %max ) {
     43	
     44	my $value = $max{ $day };
     45		print "Day=$day\tValue=$value\n";
     46	
     47	}
     48	
     49	exit;

For those interested in a programatic solution

Cheers

Nico