Using Max() function in MySql returns wrong value

Question

I've a table with SN and titles In the first query i do :

create or replace view AB as
select wrote.serial_number as SN, count(wrote.serial_number) as NumOfPapers
from wrote
group by wrote.serial_number;

and i DO get a view with SN and the count of the titles per SN than i want to get the ROW with the most titles (max of count) as : ID | max(count) so i do :

select AB.SN, max(AB.NumOfPapers)
from AB

but i get the max number of the counts with the first row person's SN (not the real SN with the most titles)

I have to use max() ... thanks for the suggestions using 'order by' but they are not relevant here

What am i doing wrong ?

Thanks !

• Ignacio Vazquez-Abrams: You forgot to provide criteria for selecting the appropriate SN.
• nos: select AB.SN, max(AB.NumOfPapers) from AB is not valid SQL. (though mysql will not complain about it). You forgot the group by clause.
• SagiLow: It seems valid in all the websites i looked for the max() syntax.
• user330315: It is invalid. Read this to understand why MySQL allows despite being invalid (and the consequences): rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html
• BellevueBob: @SagiLow The MAX function by itself does not require a GROUP BY clause, but one is required if you ask for other columns in the SELECT clause. SELECT MAX(NumOfPapers) FROM ab is perfectly valid because you are asking for the result of an analytical function across the entire table.

Answer 1

I think you want something like this:

SELECT sn
FROM ab
ORDER BY NumOfPapers DESC
LIMIT 1

• SagiLow: If i use the order by on the created view and limit it to 1 it will work as well, but it's like i'm not using the max() ... just ordering and taking the highest... shouldn't the max() return the complete row ?
• echo_Me: look what i suggested you sagilow , u will use max
• SagiLow: That didn't work ... it returned the first SN on the list together with the highest SN exists... (i think as expected?), i'm not sure if i was clear enough but i DO look for the max COUNT ...

Answer 2

The reason you're only getting the first row's count of NumOfPapers is because you are also selecting the ab.serial number. So, I think this will give you what you're looking for:

select AB.SN, AB.NumOfPapers FROM AB order by AB.NumOfPapers desc limit 1;

• SagiLow: There are the query requirements, anyway, the simplest reason, is if there are multiple max ?

Answer 3

create or replace view AB as
select wrote.serial_number as SN, count(wrote.serial_number) as NumOfPapers
from wrote
group by wrote.serial_number 
ORDER by NumOfPapers DESC;

and

select AB.SN, AB.NumOfPapers from AB LIMIT 1

Answer 4

you are using NumOfPapers which is count of serial_number

 select AB.SN, max(wrote.serial_number)
 from AB, wrote

• SagiLow: That didn't work ... it returned the first SN on the list together with the highest SN exists... (i think as expected?), i'm not sure if i was clear enough but i DO look for the max COUNT ...
• echo_Me: please show your table schema and how u expect the results , betetr give an example from your table , for we understand good .

Answer 5

I think i got it figured :

create or replace view AB as
select wrote.serial_number as SN, count(wrote.serial_number) as NumOfPapers
from wrote
group by wrote.serial_number;
select AB.SN, AB.NumOfPapers
from AB
where AB.NumOfPapers = (select max(AB.NumOfPapers)
 from AB)

Thank you all for your help, you really guided me there !

• BellevueBob: Yes, this will return all rows that have the max value; that's slightly different from your original question, which suggested you wanted one row.

Answer 6

I believe your second query asks your database for every AB.SN from AB and the max(AB.NumOfPapers) Have you tried with a where statement?

select AB.SN, max(AB.NumOfPapers)
from AB
where AB.NumOfPapers = max(AB.NumOfPapers)

(I haven't tested this though, some adjustments might be needed)

• SagiLow: I tried it before, i get an error for using the group function max() unproperly...