471,306 Members | 902 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,306 software developers and data experts.

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!
Jul 17 '05 #1
3 3422
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.
Jul 17 '05 #2
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)
Jul 17 '05 #3
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>
Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

19 posts views Thread by Ben Gribaudo | last post: by
5 posts views Thread by luke | last post: by
4 posts views Thread by Jeffrey Davis | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.