how to count max in SQL?

[JaNE]

Maybe this isn't the most apropriate place to ask mysql related
question, but I think it is close enought to most php users and I always
got find answers on this group.

well, I need to count how meny rows have some maximum number. here is
exact situation (striped down table) (pictID, jpeg name, #of votes)

pict1, name1.jpg, 4
pict2, name2.jpg, 22
pict3, name3.jpg, 5
....
pict7, name7.jpg, 8
pict8, name8.jpg, 22
pict9, name9.jpg, 9

so, I need query (or set of querys) which will give me back rows 2 and 8
(I know, rows 1 and 7, but for easyer description, please let forget for
moment that first is 0)

I know what to do if I have only one picture with maximum votes:
SELECT * FROM picts ORDER BY votes DESC LIMIT 1
(maybe not the best solution, but is working).

now, I wanted to count how meny rows have MAX of votes, but I was unable
to do that with my knowlage...
SELECT max(votes)...
and than
$TotalMax=mysql _num_rows($resu lt);
in any combination (list), (while(list(too (much...) only failures...

at the end I wanted to do SELECT with LIMIT so I can drow only those
wining pictures:

$query1 = "THAT one I don\'t KNOW";
$result1 = mysql_query ($query1) or die ("Query failed...");
list($new_limit ) = mysql_num_rows( $result1);

//and now:

$query2 = "SELECT * FROM picts ORDER BY votes DESC LIMIT $new_limit";
$result2 = mysql_query ($query2) or die ("Query failed...");
list($pict_data 1,$pict_data2,$ pict_data3) = mysql_fetch_arr ay($result2);

can anyone help me with that 1st query?
or anyone have easyer solution?

oh yes, I can not tell how meny pictures will have maximum number of
votes. so far (in 4 years of manual handling of my site) there vere only
3 moments with 2 pictures sharing wining position, never more... normaly
just one.

tnx.
Janko

--
Jan ko?
http://fotozine.org
--

[CJ Llewellyn]

"JaNE" <no****@mail.do t> wrote in message
news:1go8ueg.14 utiomdsm5agN%no ****@mail.dot.. .

Maybe this isn't the most apropriate place to ask mysql related
question, but I think it is close enought to most php users and I always
got find answers on this group.

well, I need to count how meny rows have some maximum number. here is
exact situation (striped down table) (pictID, jpeg name, #of votes)

pict1, name1.jpg, 4
pict2, name2.jpg, 22
pict3, name3.jpg, 5
...
pict7, name7.jpg, 8
pict8, name8.jpg, 22
pict9, name9.jpg, 9

so, I need query (or set of querys) which will give me back rows 2 and 8
(I know, rows 1 and 7, but for easyer description, please let forget for
moment that first is 0)

I know what to do if I have only one picture with maximum votes:
SELECT * FROM picts ORDER BY votes DESC LIMIT 1
(maybe not the best solution, but is working).

now, I wanted to count how meny rows have MAX of votes, but I was unable
to do that with my knowlage...
SELECT max(votes)...
and than
$TotalMax=mysql _num_rows($resu lt);
in any combination (list), (while(list(too (much...) only failures...

at the end I wanted to do SELECT with LIMIT so I can drow only those
wining pictures:

$query1 = "THAT one I don\'t KNOW";
$result1 = mysql_query ($query1) or die ("Query failed...");
list($new_limit ) = mysql_num_rows( $result1);

//and now:

$query2 = "SELECT * FROM picts ORDER BY votes DESC LIMIT $new_limit";
$result2 = mysql_query ($query2) or die ("Query failed...");
list($pict_data 1,$pict_data2,$ pict_data3) = mysql_fetch_arr ay($result2);

can anyone help me with that 1st query?
or anyone have easyer solution?

oh yes, I can not tell how meny pictures will have maximum number of
votes. so far (in 4 years of manual handling of my site) there vere only
3 moments with 2 pictures sharing wining position, never more... normaly
just one.

If your version of mysql supports subqueries (4.1 +)

select * FROM picts WHERE votes = (select max(votes) as mvotes FROM picts
GROUP BY votes ORDER BY mvotes DESC LIMIT 1);

Otherwise

select max(votes) as mvotes FROM picts GROUP BY votes ORDER BY mvotes DESC
LIMIT 1;

select * FROM picts WHERE votes='22';

22 being the mvotes value from the previous query.

[JaNE]

CJ Llewellyn <in*****@exampl e.con> wrote:

Otherwise

select max(votes) as mvotes FROM picts GROUP BY votes ORDER BY mvotes DESC
LIMIT 1;

select * FROM picts WHERE votes='22';

22 being the mvotes value from the previous query.

tnx. that helped.

--
Jan ko?
http://fotozine.org
--