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 | 

[fenway]

Show us the output you want vs the output you have.

[MDanz]

this is what i want

 

+----+-----------+----------+
| id |       sid |      num |
+----+-----------+----------+
|  1 |        15 |      one |   
|  2 |        15 |      two |   
|  3 |        17 |    three |   
|  4 |        17 |     four |  

 

this is what i am getting

 

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

 

basically i want to LIMIT per `sid`.  so LIMIT 2 will be two different `sid` not two rows.

[ManiacDan]

GROUP_CONCAT turns a field into a delimited list of items.

 

Combine that with SUBSTR and other string manipulation functions and you can:

1)  use group_concat to make your third column a comma-delimited list of all the num fields.

2)  use strpos (or equivalent) to find the location of the second comma

3)  Use substr to limit your delimited list to the characters up to the second comma (effectively, the first two elements)

 

-Dan