Because that wouldn't give the correct results.
I want the top 5 rows for EACH id.
A short example would be,
mytable:
Id Val Hits
1 a 10
1 b 15
1 c 17
2 q 200
2 r 205
2 s 101
2 t 50
3 zz 10
3 yy 20
3 xx 30
3 ww 40
3 uu 50
select max(2,hits) from mytable
Id
1 b 15
1 c 17
2 q 200
2 r 205
3 ww 40
3 uu 50
This is the top two rows for EACH id...
without this functionality i have to do
select distinct cid from mytable;
foreach(cid) {
select * from mytable where cid='$cid' order by hits desc limit 2;
}
Which means the table is reread over and over. With this extra function we
could reduce the reads to at best once.
Richard
----- Original Message -----
From: "Christopher Thompson" <[EMAIL PROTECTED]>
To: "Richard Clarke" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, March 04, 2002 5:56 PM
Subject: Re: extra max() function possibly very useful?
> On Monday 04 March 2002 10:50 am, Richard Clarke wrote:
> >
> > create table mytable (id int, val char(255), hits int);
> > insert some data...
> >
> > select max(5,hits) from mytable group by id;
> >
> > This would allow selecting of the top 5 rows for each id according to
the
> > hit column.
> >
> > Without this function it means the code must be written manually as,
> > foreach(id) {
> > select * from mytable where id='$id' order by hits desc limit 5
> > }
>
> Why would you do your select like that instead of:
> select * from mytable order by hits desc limit 5
> ?
>
> And for the spam filter: mysql, microsoft sucks, nusphere lawsuit. :)
---------------------------------------------------------------------
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
