CODEWITHSUNDEEP
phpmysql
Rohitashv Singhal
Rohitashv Singhal

Reputation: 4557

How to get row of the table with maximum value

I have a table as follow :

id         date     custid  billno  month   amount  balance
64      07-Jun-2015     1   102     5   9192    0
134     05-Jul-2015     1   172     6   9744    0
235     01-Aug-2015     1   277     7   4032    0
435     04-Sep-2015     1   461     8   3024    0
747     22-Sep-2015     1   597     9   2875    0
958     06-Nov-2015     1   789     10  3100    0

I want to get the row which has month max i.e. I want to get the row with month 10

If I use the following query then it gives the row with month 9

$get_lst = mysql_query("select * from `ledger` where `custid`='$custid' order by `month` DESC") or die(mysql_error());
$get_lst = mysql_fetch_assoc($get_lst);
print_r($get_lst);

Please help me what I am missing in the query ?

Upvotes: 0

Views: 47

Answers (1)

Patrick Schumacher
Patrick Schumacher

Reputation: 152

It looks like you are storing your values with the wrong type. The Date column should be type of DATE and your month column should be type of INT or even better TINYINT.

Storing month as VARCHAR causes the db to sort it as string. Ordering strings will start checking char by char (comparing '9' with '10' will result in '9' > '1').

After changing your column to either INT or TINYINT your query will work.

To increase the performance for larger tables, you could also use "LIMIT 1" in your query, in that way you won't fetch the whole table, if you only need one row.

Upvotes: 1

Related Questions