Mysql Query help

[kyllerj]

I'm trying to figure out the proper query string that would return only the first three records of each group in MySql table.

for example if my table looked like this

Field1....Field 2....Field 3

x...........xdata.....xdata
x...........xdata.....xdata
x...........xdata.....xdata
x...........xdata.....xdata
x...........xdata.....xdata
y...........ydata.....ydata
y...........ydata.....ydata
y...........ydata.....ydata
y...........ydata.....ydata
z...........zdata.....zdata
z...........zdata.....zdata
z...........zdata.....zdata
z...........zdata.....zdata
z...........zdata.....zdata

How would I return the first 3 rows of each value in Field 1 assuming i did not know the values in Field 1

Can anyone help with this?

TIA,

Kyler

[Lars_Berg]

It's yet another query that cannot be done in one step with Mysql.

$q = "select distinct field1 from t";
$c = mysql_query($q) or die (mysql_error());
while ( $row = mysql_fetch_row($c) ) {
  $qx = "select * from t where field1 = '" . $row["field1"] . "' order by field2,field3 limit 3";
//handle result for $qx 
}

First is not a relational concept, limit without order by is meaningless.

[kyllerj]

Perfect, thank you very much. I have one more question. Can that code be altered so that each additional query would return the next three rows in each group?

I believe you can use a second argument for LIMIT that names the row to start counting at, am I correct?

So the code would be somethint like.

code:--------------------------------------------------------------------------------
$i = 0
$q = "select distinct field1 from t";
$c = mysql_query($q) or die (mysql_error());
while ( $row = mysql_fetch_row($c) ) {
$qx = "select * from t where field1 = '" . $row["field1"] . "' order by field2,field3 limit \"$i\", 3";
//handle result for $qx
$i = $i +3

}

Would that be correct?

Kyler

[barand]

I had not realised that you could do that with a limit clause. I've just looked it up in the reference manual

The LIMIT clause can be used to constrain the number of rows returned by the SELECT
statement. LIMIT takes one or two numeric arguments.
If two arguments are given, the first specifies the offset of the first row to return, the
second specifies the maximum number of rows to return. The offset of the initial row
is 0 (not 1):
mysql> select from table LIMIT 5,10; # Retrieve rows 6-15
If one argument is given, it indicates the maximum number of rows to return:
mysql> select from table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.

Thanks for that.