Selecting the maximum value (MySQL query help)

[bachx]

Alright, I have a quick and probably an easy question. I want to select the maximum balance in my account_info table, along with the name of the account with the highest balance. The statement goes like this:

 

$max_query = mysql_query("SELECT name, max(balance) FROM account_info WHERE address='xxx' GROUP BY name LIMIT 1")

 

The problem is, while it returns the maximum balance, it does not return the proper name for that balance. How can I make it so that it returns the maximum balance along with the matching account name?

 

Cheers.

[trq]

Providing the name field holds that name your looking for your query looks good.

[bachx]

Weird. I'm getting the right balance but not the right name. Something must be wrong with that statement.

[bachx]

Anyone?

[pranav_kavi]

try dis,

 

select name from account_info where balance=(select max(balance) from account_info) and address='xxx'

[bachx]

That returned 0 results.

[pranav_kavi]

try tis first...

select name,balance from account_info where balance=(select max(balance) from account_info)

[beboo002]

i think there is some problem to group by statement plz drop the group by

[pranav_kavi]

r u gettin any output??

[bachx]

try tis first...

select name,balance from account_info where balance=(select max(balance) from account_info)

 

That worked. But when I try to add another condition, like "AND address='xxx'", it'll return 0 results.

[pranav_kavi]

As per ur inital post,

I want to select the maximum balance in my account_info table, along with the name of the account with the highest balance.

the query in the last post achieves the above objective.Why do u need 'AND address='xxx''?

i assume u want to display the name wid highest salary only if his address is 'xxx'..rite?

 

[bachx]

Yes, I want to add other conditions.

[pranav_kavi]

As only row is being retrieved frm the query...u can do the condition checks on the php side.it wud b much easier.

[bachx]

I'm sure there is a certain SQL query statement for such a case. Implementing it in php won't be as much effective/fast.

[pranav_kavi]

select name from account_info where balance=(select max(balance) from account_info)

 

is the 'address' column retrieved by the abov query 'xxx' ?The abov query only retrieves one row,so if the address condition fails,nothin wud b displayed.

[bachx]

The address is there, it's not supposed to fail. Anyone?

[pranav_kavi]

select name,balance from emp where balance=(select max(balance) from emp) and address='dxb'

 

i had tried the above query on one of my tables..it displayed one record with name,balance only bcos the address column had 'dxb' value.

did u try,

select name,balance from emp where balance=(select max(balance) from emp) and address like '%xxx%'

[bachx]

I figured why it was not returning any results. Your query checks if the maximum balance (for all records) is found within that address, and because it's not, it wasn't returning any values.

 

That's not what I want, I want to find out the maximum balance for a certain address, not for all the records. Hope I made it clear.

 

It should be something similar to my original statement:

SELECT name, max(balance) FROM account_info WHERE address='xxx' GROUP BY name LIMIT 1

[pranav_kavi]

i think tis s wat u want.....

 

select name,balance from account_info

where

balance = (select max(balance) from account_info

group by address

having address='xxx')

[MadTechie]

Weird. I'm getting the right balance but not the right name. Something must be wrong with that statement.

 

I have to ask but, how do you know that? as you sure the file is "name" ?

 

EDIT: its possible theirs 2 rows with the same balance

[bachx]

I'm sure, I've created a sample database and I know the values in it. There are no identical rows/etc.

[MadTechie]

does seam weird..

 

maybe posting a small DB dump

[bachx]

Anyone else can figure this out?