Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- how to count max in SQL?
December 4, 2004, 6:41 am
rate this thread
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...
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
$query1 = "THAT one I don\'t KNOW";
$result1 = mysql_query ($query1) or die ("Query failed...");
list($new_limit) = mysql_num_rows($result1);
$query2 = "SELECT * FROM picts ORDER BY votes DESC LIMIT $new_limit";
$result2 = mysql_query ($query2) or die ("Query failed...");
list($pict_data1,$pict_data2,$pict_data3) = mysql_fetch_array($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
Re: how to count max in SQL?
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);
select max(votes) as mvotes FROM picts GROUP BY votes ORDER BY mvotes DESC
select * FROM picts WHERE votes='22';
22 being the mvotes value from the previous query.