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]

Re: how to count max in SQL?

"JaNE" <not_me@mail.do t> wrote in message
news:1go8ueg.14 utiomdsm5agN%no t_me@mail.dot.. .[color=blue]
> 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.[/color]

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]

Re: how to count max in SQL?

CJ Llewellyn <invalid@exampl e.con> wrote:
[color=blue]
>
> 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.[/color]

tnx. that helped.

--
Jan ko?

http://fotozine.org

--