Hi!
>>>>> "Richard" == Richard Clarke <[EMAIL PROTECTED]> writes:
Richard> Because that wouldn't give the correct results.
Richard> I want the top 5 rows for EACH id.
Richard> A short example would be,
Richard> mytable:
Richard> Id Val Hits
Richard> 1 a 10
Richard> 1 b 15
Richard> 1 c 17
Richard> 2 q 200
Richard> 2 r 205
Richard> 2 s 101
Richard> 2 t 50
Richard> 3 zz 10
Richard> 3 yy 20
Richard> 3 xx 30
Richard> 3 ww 40
Richard> 3 uu 50
Richard> select max(2,hits) from mytable
Richard> Id
Richard> 1 b 15
Richard> 1 c 17
Richard> 2 q 200
Richard> 2 r 205
Richard> 3 ww 40
Richard> 3 uu 50
Richard> This is the top two rows for EACH id...
Richard> without this functionality i have to do
Richard> select distinct cid from mytable;
Richard> foreach(cid) {
Richard> select * from mytable where cid='$cid' order by hits desc limit 2;
Richard> }
You may be able to use something like the following (not tested) trick
to do this:
SET @b=0;
SELECT *,(@a:= IF(@b=id,@a+1,1)) as cnt, @b:=id from mytable order by
id having cnt<5;
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
