Numbering rows in mysql

[mcavic]

Does anyone know of a function in MySQL that will number the rows in a table dynamically upon a select statement? For example, something like:

select field1, rownum() from mytable;

field1 rownum 
------ ------ 
50 1 
25 2 
77 3 
333 4 

The reason I want this is so that I can say:

select avg(field1) from mytable where rownum <= 5;

And it'll average the first 5 rows. Or is there another way to do the average?

[Nutter]

What about

SELECT AVG(field) FROM table LIMIT 5

then...

SELECT AVG(field) FROM table LIMIT 5, 5

for the next 5, etc?

- Ryan

[mcavic]

I tried that, but it doesn't seem to work. It evaluates the Limit only after the rest of the statement has been executed.

[grandpa]

You could try this,

select field1 from mytable where x is y;

With the right select you read 5 rows in a while loop,
get the average, and read 5 more.

[mcavic]

Yeah, I can calculate the average myself in a loop. Oracle has a function that'll do it with just one select.

[gmiller]

Don't newer versions of MySQL have support for subselects?