SELECTing the 'middle' row in a table (maybe with php?)

[ams]

A table contains records of Ice Cream flavors. There is a primary id
field (INT) and a varchar for flavor.

I need to get the row in the very middle of the table, however I can't
assume that the table's primary id field will be entirely sequential
across the table because some records may have been deleted.

Say there are 10 records in the table and the last primary id is 24.
I can't reliably devide 24 by 2 to assume the middle row is 12 because
perhaps the first 10 records were deleted.. that would mean my
"middle" row is actaully nearer to the beginning of the record set.

Is there something I can do with the total number of rows in the
table, to get the 'middle'-most row?

If I'm being too confusing ask and I'll reexplain.

Thanks!

[Disco Plumber]

The Pillsbury Doughboy, speaking through ams, wrote:

Is there something I can do with the total number of rows in the
table, to get the 'middle'-most row?

offhand, the first thing that comes to mind, which may not be the most
efficient (error-checking left out for brevity):

$q = mysql_query("SELECT COUNT(*) AS total FROM ice_cream");
$rec = mysql_fetch_assoc($q);
$total = $rec['total'];
$middle = floor($total / 2);
$q = mysql_query("SELECT * FROM ice_cream LIMIT $middle,1");

note that I'm sure this works if you are ORDERing the table, but I have
no idea if an un-ORDERed SELECT will always return the rows in the same
order. I would be doubtful that it is dependable in all cases.

/joe
--
In Psi U, Rob Solomon links to the website of Rob Solomon for Scott
Watkins's network cable. Shad Hashmi memorizes the configuration of the
gi-normous memory.

[Andy Hassall]

On Sat, 8 Nov 2003 22:10:59 +0000 (UTC), Disco Plumber <sc**@moralminority.org>
wrote:

note that I'm sure this works if you are ORDERing the table, but I have
no idea if an un-ORDERed SELECT will always return the rows in the same
order. I would be doubtful that it is dependable in all cases.

Correct - order is not guaranteed in any way in SQL unless you have an ORDER
BY clause.

(Depends on the implementation exactly how unpredictable it is without an
ORDER BY)

A more portable way could be to just fetch all the IDs into an array, pick the
middle one, and do a select for that ID. Whether this is better or worse
depends on the volume of data.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[127.0.0.1]

Disco Plumber wrote:

Is there something I can do with the total number of rows in the
table, to get the 'middle'-most row?

offhand, the first thing that comes to mind, which may not be the most
efficient (error-checking left out for brevity):

Does mySQL have a median statistics function ... would return the
middle value of a single valued column.

--
Spam:newsgroup(at)cr*********@verisign-sux-klj.com
EMail:<0110001100101110011000100111010101110010011 010110
11001010100000001100011011100100110000101111010011 011100
11000010111001000101110011000110110111101101101001 00000>