SEO

If I have a trivial table data where describe data
gives
mysql> describe data;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| flux | int(11) | YES | | NULL | |
| bin | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

and I have populated the tables with some values,
I can get the largest flux per bin by
select max(flux),bin from data group by bin;

What would ask for to get the largest three values
in each bin?

Greg Hennessy:

> What would ask for to get the largest three values
> in each bin?

select bin
from data
order by bin desc
limit 3

--
Erick

On 2008-04-27, Erick T Barkhuis <erick.use-net@ardane.c-o-m> wrote:
> Greg Hennessy:
>
>> What would ask for to get the largest three values
>> in each bin?
>
> select bin
> from data
> order by bin desc
> limit 3

That does seem to get me what I think it should.

mysql> select * from data;
+------+------+
| flux | bin |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 2 | 2 |
| 4 | 2 |
| 6 | 2 |
| 8 | 2 |
| 4 | 1 |
+------+------+
8 rows in set (0.00 sec)

mysql> select flux,bin from data order by bin desc limit 3;
+------+------+
| flux | bin |
+------+------+
| 2 | 2 |
| 4 | 2 |
| 6 | 2 |
+------+------+
3 rows in set (0.00 sec)

While I get the three smallest flux in bin 2, I get no results
from bin one.

On 2008-04-27, Greg Hennessy <greg.hennessy@cox.net> wrote:
> That does seem to get me what I think it should.

That *doesn't* seem to get me what I think it should. I hate typoes.

On Apr 27, 3:21 pm, Greg Hennessy <greg.henne...@cox.net> wrote:
> On 2008-04-27, Greg Hennessy <greg.henne...@cox.net> wrote:
>
> > That does seem to get me what I think it should.
>
> That *doesn't* seem to get me what I think it should. I hate typoes.

SELECT t1.* FROM data t1
LEFT JOIN data t2
ON t1.bin = t2.bin
AND t1.flux >= t2.flux
GROUP BY t1.bin,t1.flux
HAVING COUNT(t1.bin) <=3;

On 2008-04-27, strawberry <zac.carey@gmail.com> wrote:
> SELECT t1.* FROM data t1
> LEFT JOIN data t2
> ON t1.bin = t2.bin
> AND t1.flux >= t2.flux
> GROUP BY t1.bin,t1.flux
> HAVING COUNT(t1.bin) <=3;

Thank you. With the minor flipping of a greater than to a less than
that does what I wanted. Now to figure out *why* it does what I
want.

On Apr 27, 4:00 pm, Greg Hennessy <greg.henne...@cox.net> wrote:
> On 2008-04-27, strawberry <zac.ca...@gmail.com> wrote:
>
> > SELECT t1.* FROM data t1
> > LEFT JOIN data t2
> > ON t1.bin = t2.bin
> > AND t1.flux >= t2.flux
> > GROUP BY t1.bin,t1.flux
> > HAVING COUNT(t1.bin) <=3;
>
> Thank you. With the minor flipping of a greater than to a less than
> that does what I wanted. Now to figure out *why* it does what I
> want.

It may help you to understand it by rewriting it like this:

SELECT t1.*, COUNT(t1.bin) rank
FROM data t1
LEFT JOIN data t2
ON t1.bin = t2.bin
AND t1.flux <= t2.flux
GROUP BY t1.bin,t1.flux
ORDER BY bin, rank;