[SOLVED] Display the greatest number

[ted_chou12]

okay, i will give an example at the start:
I have 3 apples
You have only 1 apple
I want to display the name which has the most apple, whos name is ted
$sql = "SELECT * FROM appletable WHERE name = 'Ted'";
$result = mysql_query($sql);
$applesofme = mysql_num_rows($result);
echo $appleofted;

This would give the amount of rows that belong to Ted...

$sql = "SELECT * FROM appletable WHERE name = 'You'";
$result = mysql_query($sql);
$applesofyou = mysql_num_rows($result);

This would give the amount of rows that belong to you...

but I wish to display the name with more apple
apple is the number of rows by the way
and Name is just one of the column in mysql.
Thanks
Ted

[ted_chou12]

please refresh the page to see the editing done

[AndyB]

I've read that three times and I still can't figure out what it is you want to know .....  name, ted, apple, rows, or what???  Perhaps we need a more realistic example including sample data, and fewer imaginary variable names?

[chiprivers]

[quote author=AndyB link=topic=119900.msg491466#msg491466 date=1167088408]
I've read that three times and I still can't figure out what it is you want to know .....  name, ted, apple, rows, or what???  Perhaps we need a more realistic example including sample data, and fewer imaginary variable names?
[/quote]

I think what he wants is something like

SELECT * FROM appletable GROUP BY name

but then with an extra value returned with each row to show how many records for each name!

[ERuiz]

Let me jump in, since Ted is helping me out with this problem.

I have a table named "pirep" and the first 5 fields on this table are:

IDPirep    |    CreatedOn    |      IDPilot    |    PilotName    |    AircraftTitle

An example for rows would be:


20061225 | 2006-12-25 13:08:01 |  1    | John Doe | American Airlines B767-300ER
20061225 | 2006-12-25 13:08:01 |  3    | Efrain Ruiz | American Airlines B767-300ER
20061225 | 2006-12-25 13:08:01 |  3    | Efrain Ruiz | American Airlines B767-300ER
20061225 | 2006-12-25 13:08:01 |  2    | Jane Doe | American Airlines B767-300ER
20061225 | 2006-12-25 13:08:01 |  4    | Tim Smith | American Airlines B767-300ER

What I need to know, is how to make a query which will add all rows for EACH unique IDPilot value and then display the PilotName with the most rows.

Using the information above, the pilotname that should be reported as having the most flights is Efrain Ruiz, since I have 2 rows.

[Barand]

SELECT pilotname, COUNT(*) as rowcount
FROM pirep
GROUP BY pilotname
ORDER BY rowcount DESC
LIMIT 1

[ERuiz]

That did the trick! Thanks!

[ted_chou12]

what is LIMIT, i havent get to it before, could you explain please?

[chiprivers]

[quote author=ted_chou12 link=topic=119900.msg491493#msg491493 date=1167092477]
what is LIMIT, i havent get to it before, could you explain please?
[/quote]

LIMIT sets the number of records returned, in this case it was only one record so by using the ORDER DESC syntax also, it returned just the first record, the largest.

[ted_chou12]

oh, so if we have LIMIT 2, than it will give two results instead of 1, but two greatest?

[Barand]

[quote="mysql manual - you should read it sometime"]
The LIMIT clause can be used to constrain the number of rows returned by the SELECT  statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5;    # Retrieve first 5 rows

In other words, LIMIT row_count is equivalent to LIMIT 0, row_count.
[/quote]