MySQL LIMIT per value help

[MDanz]

With the below query I'd get the results one and three. How do I get the results for `sid`; 15 and 17? I can't use a WHERE because I won't know the `sid`.

A better way of explaining is, how do I LIMIT per `sid` without grouping?

mysql_query("SELECT *
             FROM   `mytable`
             GROUP  BY `sid`
             ORDER  BY `sid` ASC
             LIMIT  0, 2");

+----+-----------+----------+
| id |       sid |      num |
+----+-----------+----------+
|  1 |        15 |      one |   
|  2 |        15 |      two |   
|  3 |        17 |    three |   
|  4 |        17 |     four |  
|  5 |        18 |     five |   
|  6 |        18 |      six |

[|-|x]

Something like this would work, you may also be able to make use of the SELECT DISTINCT predicate if it helps - however, I think using the GROUP BY clause is cleaner and likely more efficient.

food for thought anyway...

select * from mytable where id=(
    select id from mytable where sid=(
        select Min(sid) from mytable
    ) limit 0,1
) or id=(
    select id from mytable where sid=(
        select Min(sid) from mytable where not sid=(
            Select Min(sid) from mytable
        )
    )limit 0,1
);

[smantscheff]

It is not clear how the output of your query should look like.

[|-|x]

Sorry, allow me to explain...

The first subquery will return the id of the first record with the lowest sid The second subquery will return the id of the first record with the second lowest sid so the actual resultant dataset will look like this:

+----+-----------+----------+
| id |       sid |      num |
+----+-----------+----------+
|  1 |        15 |      one |   
|  3 |        17 |    three |

which, from my understanding, was the desired outcome.

However as I mentioned the use of nested subqueries is significantly less efficient that using the original group by filter, and may not be appropriate or practical depending on the circumstance. If anyone can see a better way, please post. :)

[smantscheff]

I didn't know that you can use LIMIT in subqueries. Must be new.
Here my alternative:

select m1.*
from mytable m1, mytable m2 
where m1.id=m2.id
group by m2.sid
limit 2;

Test case:

use test;
drop table if exists mytable;
create table mytable (id integer, sid integer, num varchar(255));
insert into mytable values 
('1','15','one'),
('2','15','two'),
('3','17','three'),
('4','17','four'),
('5','18','five'),
('6','18','six');

select * from mytable where id=(
    select id from mytable where sid=(
        select Min(sid) from mytable
    ) limit 0,1
) or id=(
    select id from mytable where sid=(
        select Min(sid) from mytable where not sid=(
            Select Min(sid) from mytable
        )
    )limit 0,1
);

select m1.*
from mytable m1, mytable m2 
where m1.id=m2.id
group by m2.sid
limit 2;

This works only because of a (in my opinion) mis-interpretation of the GROUP BY clause in MySQL.

[|-|x]

You don't really need the second mytable, as MDanz is essentially achieving the same thing in his original query.

I agree that this only works because MySQL doesn't restrict the select to true GROUP clauses/fields.

I am also surprised to see (in my own testing just now) that the subquery version runs consistently 0.0001 sec faster than the group by version. (we are talking a difference of 0.0004 to 0.0005 seconds here, it is only a tiny dataset after all). I'd be interested to see if your results are similar as I would have thought the subqueries to be less efficient in execution.

[smantscheff]

You're right about the superfluous 2nd table. It grew out of some testing.
I didn't do any performance testing. I assume that differences with such small datasets are due mainly to the execution time for parsing and optimizing the query.