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]

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

The Pillsbury Doughboy, speaking through ams, wrote:[color=blue]
>
> Is there something I can do with the total number of rows in the
> table, to get the 'middle'-most row?[/color]

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

$q = mysql_query("SE LECT COUNT(*) AS total FROM ice_cream");
$rec = mysql_fetch_ass oc($q);
$total = $rec['total'];
$middle = floor($total / 2);
$q = mysql_query("SE LECT * 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]

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

On Sat, 8 Nov 2003 22:10:59 +0000 (UTC), Disco Plumber <scag@moralmino rity.org>
wrote:
[color=blue]
>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.[/color]

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 (andy@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]

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

Disco Plumber wrote:
[color=blue][color=green]
> > Is there something I can do with the total number of rows in the
> > table, to get the 'middle'-most row?[/color]
>
> offhand, the first thing that comes to mind, which may not be the most
> efficient (error-checking left out for brevity):[/color]

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

--
Spam:newsgroup( at)craznar.com@ verisign-sux-klj.com
EMail:<01100011 001011100110001 001110101011100 10011010110
110010101000000 011000110111001 001100001011110 10011011100
110000101110010 001011100110001 101101111011011 0100100000>