Re: extra max() function possibly very useful?
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> IdValHits
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
Fw: extra max() function possibly very useful?
Spambusting: MySQL
> Richard,
>
> There has been talk of this before.
> (I haven't checked but) wouldn't be surprised if it's on the ToDo list
> - did you know that such info is available within the manual?
> If it's not, then propose such as a 'wish list'.
>
> Regards,
> =dn
>
> - Original Message -
> From: "Richard Clarke" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: 04 March 2002 19:09
> Subject: Re: extra max() function possibly very useful?
>
>
> > Correct.
> >
> > Richard
> >
> >
> > - Original Message -
> > From: "DL Neil" <[EMAIL PROTECTED]>
> > To: "Richard Clarke" <[EMAIL PROTECTED]>; "Christopher Thompson"
> > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > Sent: Monday, March 04, 2002 6:34 PM
> > Subject: Re: extra max() function possibly very useful?
> >
> >
> > > LIMIT to be applied to each group result, instead of to the final
> > > resultset.
> > > Correct?
> > > =dn
> > >
> > >
> > > > Because that wouldn't give the correct results.
> > > > I want the top 5 rows for EACH id.
> > > ...
> > > > 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.
> > >
> >
> > SQL
> >
> >
>
> -
> > 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
> >
> >
>
-
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
Re: extra max() function possibly very useful?
Richard,
There has been talk of this before.
(I haven't checked but) wouldn't be surprised if it's on the ToDo list
- did you know that such info is available within the manual?
If it's not, then propose such as a 'wish list'.
Regards,
=dn
- Original Message -
From: "Richard Clarke" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: 04 March 2002 19:09
Subject: Re: extra max() function possibly very useful?
> Correct.
>
> Richard
>
>
> - Original Message -
> From: "DL Neil" <[EMAIL PROTECTED]>
> To: "Richard Clarke" <[EMAIL PROTECTED]>; "Christopher Thompson"
> <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Monday, March 04, 2002 6:34 PM
> Subject: Re: extra max() function possibly very useful?
>
>
> > LIMIT to be applied to each group result, instead of to the final
> > resultset.
> > Correct?
> > =dn
> >
> >
> > > Because that wouldn't give the correct results.
> > > I want the top 5 rows for EACH id.
> > ...
> > > 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.
> >
>
> SQL
>
>
> -
> 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
>
>
-
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
Re: extra max() function possibly very useful?
Correct.
Richard
- Original Message -
From: "DL Neil" <[EMAIL PROTECTED]>
To: "Richard Clarke" <[EMAIL PROTECTED]>; "Christopher Thompson"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, March 04, 2002 6:34 PM
Subject: Re: extra max() function possibly very useful?
> LIMIT to be applied to each group result, instead of to the final
> resultset.
> Correct?
> =dn
>
>
> > Because that wouldn't give the correct results.
> > I want the top 5 rows for EACH id.
> ...
> > 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.
>
SQL
-
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
Re: extra max() function possibly very useful?
Because that wouldn't give the correct results.
I want the top 5 rows for EACH id.
A short example would be,
mytable:
IdValHits
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
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
extra max() function possibly very useful?
Hi,
What would the plausability of a function like this being implemented in the
future.
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
}
which is obviously quite inefficient where the domain of id becomes large.
Any input from the developers about the possibility of this functionality
would be much appreciated.
Richard
p.s. here is the word mysql to get past the intelligent spam filter.
-
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
