pull out first X unique fields records and rows associated with them?

[yamipoli]

sorry for bad phrasing but I don't know how else to explain it.

 

-----------------------------
| Fruit | Status |
-----------------------------
| Apple | Bitten |
-----------------------------
| Apple | Whole |
-----------------------------
| Orange | On Fire |
-----------------------------
| Orange | Not On Fire |
-----------------------------
| Lemon | On A Tricycle |
-----------------------------

 

 

What I want to do is find the first X fruits (assume X = 2 for this), and each row that has that key as well. So for example I want the first four rows because they are 'apple' and 'orange'. I can't specify based on Fruit name or any such easy ways out.

[smerny]

i'm not sure if there is a way to do it with SQL, there probably is, but with PHP you could do it..

 

i'd do something like...

 

$last_fruit = "";
$num_fruits = 0;

while($num_fruits < 3){
  $row = mysql_fetch_assoc($result)
  if($row['fruit'] != $last_fruit){
    $last_fruit = $row['fruit'];
    $num_fruits++;
  }
  if($num_fruits < 3){
    //whatever you want to do with it
  }
}

 

of course this is after whatever sql returns the above table...

maybe not how others would do it, but it would serve your purpose

[yamipoli]

Actually I have a way with php already, but I was afraid I was missing something simple basically and wanted to check.

[smerny]

ah.. i'd think to do something like

 

SELECT * FROM fruit_table WHERE FRUIT IN(

SELECT FRUIT FROM fruit_table

  GROUP BY FRUIT LIMIT 2)

 

but mysql doesn't allow limits within subqueries, and i'm not sure what the alternative is

[yamipoli]

Hmmm... alright then, is there just a way to see how many of each unique fields there are (like, getting back a list like apple: 2, orange:2 lemon:1 ? i might could use that instead of I recode some stuff.

[smerny]

SELECT Fruit, COUNT( * ) AS count

FROM fruit_table

GROUP BY Fruit

[yamipoli]

Thank you muchly for your help