[compsci]

Hello all,

I have several tables and i would like for each table to check a particular coulumn and return the row with the highest integer in the column that was checked.

So for one table, i have a column called biggest. How do i test which row has the biggest integer in that column? What would be the SQL statement? Would you use GREATEST() function??

This is what i have so far and it doesn't work:

PHP Code:

<?php

require_once("connect2database.php");

for($i=0; $i<count($table); $i++){

$table = $table[$i];

$query = sprintf("SELECT subject, date FROM $table WHERE GREATEST(biggest) LIMIT 1"); //need to add ordering
$result = @mysql_query($query);

while($row = mysql_fetch_array($result)){
echo $row['subject']."   ".$row['date'];
}
}
?>

I will appreciate any help.

Thanks all

[swapnilclarion]

Hi,

Try using below query:

$query = sprintf("SELECT subject, date FROM $table ORDER BY biggest DESC LIMIT 1");

Thanks,

[georgev]

What's wrong with simply using

SELECT Max(biggest) FROM MyTable

[r937]

Originally Posted by georgev

What's wrong with simply using...

because that doesn't return the entire row, as required

[georgev]

Ahh, touché... Yep, ignore my response then

[aschk]

I'm guessing this "biggest" column is unique????
i.e. it can only contain one of each of the following 1,2,3,4,5,6......to infinity

So, your SQL could be

SELECT `subject`
     , `date`
FROM <table>
GROUP BY `biggest` HAVING biggest = (SELECT MAX(biggest) FROM <table>);

"i think"


note : and if it's not unique then how do you know you're getting the right row when you select the largest "biggest" value ?

[r937]

SELECT subject, date 
  FROM $table 
 WHERE biggest =
       ( SELECT MAX(biggest) FROM $table )

[compsci]

Originally Posted by swapnilclarion

Hi,

Try using below query:

$query = sprintf("SELECT subject, date FROM $table ORDER BY biggest DESC LIMIT 1");

Thanks,

Your logic is one that i understand but some of the columns can be null (contain nothing) and that means it will always return one row from every table. Any way to modify it?

[compsci]

Originally Posted by r937

SELECT subject, date 
  FROM $table 
 WHERE biggest =
       ( SELECT MAX(biggest) FROM $table )

This sort of works but it is returning some rows that have equal values in the biggest columns. For example,two rows have a value of 0 and it returns both. Shall I add a limit. like this:

  SELECT subject, date 
  FROM $table 
FROM $table 
 WHERE biggest =
       ( SELECT MAX(biggest) FROM $table ) LIMIT 1

[r937]

if two rows have the biggest value, then both should be returned

remember, you said you wanted the row, not the value

if there are two rows with the biggest value, then you cannot really pick just one of them

[compsci]

Originally Posted by r937

if two rows have the biggest value, then both should be returned

remember, you said you wanted the row, not the value

if there are two rows with the biggest value, then you cannot really pick just one of them

I also said "the row with the highest integer" - meaning one row should be returned. - So would the "LIMIT 1" work. I tried it and it seems to be working?

[r937]

yes, it "works" but it is not correct in my opinion

let's say that you are taking an SQL course, and on the final exam, the teacher says there will be a prize for the best mark, i.e. the person with the highest mark... and let's say both you and Suzy get the highest mark, and the teacher gives the prize to Suzy... and you're okay with that?????

[compsci]

Originally Posted by r937

yes, it "works" but it is not correct in my opinion

let's say that you are taking an SQL course, and on the final exam, the teacher says there will be a prize for the best mark, i.e. the person with the highest mark... and let's say both you and Suzy get the highest mark, and the teacher gives the prize to Suzy... and you're okay with that?????

I would force the teacher to buy another prize!

I see where your coming from but for my case i only need one result per table. Adding the result from the tables (7 tables) then i will have 7 results and not just one.

[sco08y]

Originally Posted by compsci

but for my case i only need one result per table.

So add a tiebreaker by means of an ORDER BY clause. Otherwise your result is undefined. Do we really need to explain why undefined results are a bad thing?

[compsci]

Originally Posted by sco08y

Do we really need to explain why undefined results are a bad thing?

My result isn't undefined.