Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Unanswered: Get row that contains largest number

    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

  2. #2
    Join Date
    May 2007
    Posts
    8
    Hi,

    Try using below query:

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

    Thanks,

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What's wrong with simply using
    Code:
    SELECT Max(biggest) FROM MyTable
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    What's wrong with simply using...
    because that doesn't return the entire row, as required
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh, touch&#233;... Yep, ignore my response then
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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
    Code:
    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 ?
    Last edited by aschk; 09-27-07 at 10:39.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT subject, date 
      FROM $table 
     WHERE biggest =
           ( SELECT MAX(biggest) FROM $table )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote 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?

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    Code:
    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:

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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote 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?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote 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.

  14. #14
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote 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?

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by sco08y
    Do we really need to explain why undefined results are a bad thing?
    My result isn't undefined.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •