SELECT *, COUNT(productid) AS total FROM products LIMIT total, 3
I think MySQL doesnt allow varibles in the LIMIT part
my google-fu gave me this:
MySQL Bugs: #8094: Variables in the LIMIT Clause
even if you did manage to obtain a COUNT from the products table, putting that number as the first paramter in the LIMIT clause indicates how many rows to skip, and there just ain’t gonna be any 3 rows after the last one
besides, LIMIT usually doesn’t make sense without ORDER BY
but your main problem is the dreaded, evil “select star”
what do you really want – a COUNT, or all rows? ya can’t have both
If you are counting, you want a GROUP BY.
i want to select 3 last row and i have to count total row to do it… is there any way to do it ?
last 3 based on what?
lets say i want to select product with active status and i want display last 3 row of result
there is no order in a database table
so “last 3” makes no sense unless you do it with reference to the values in a column
3 latest –> select … order by date descending limit 3
3 smallest –> select … order by size ascending limit 3
get the idea?
Well that’s not quite true, is it. The database infers a FIFO order by default if an index isnt defined.
i want select last 3 row of the result but display it as ascending… for example facebook comment… it will display 4 last row but in ascending order…
that would be done by date, descending. You then take the result and reverse the order when you get it into PHP or whatever language you’re retrieving the data with.
in a word… no
if you actually believe that, then i’ve got a bridge in brooklyn and some nice dry property in florida you might be innerested in
Run SELECT * on a database table without an index a few times.
“Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.”
(MySQL Manual 5.6, 7.3.1)
So yes, there is an implied index - the order of data in the table file, which would be FIFO.
like i said, if you believe that…
mysql places the rows onto the disk wherever there happens to be space available
without getting into a lot of detail, this is ~not~ always going to be “fifo”
furthermore, if you do not include an ORDER BY clause in your SELECT statement, mysql can return the rows in whatever sequence it finds convenient
can you be sure that rows that happen to be in a buffer will be re-retrieved in fifo sequence? or could you not imagine that mysql will return the rows in the buffer first, and then go and retrieve the others off the disk
DO NOT rely on anything other than ORDER BY to retrieve rows in a given sequence
if you want fifo, use a datetime column
First off - You should always be using an order by for sequencing. Agrees with r937, and never disagreed with him on that point.
Now i think i’m wandering a bit from the original post here, but I am now curious.
How does mySQL retrieve those rows its randomly scattered around the disk? Cause, in my head, i say “The table file lists the memory addresses for the data, wherever it is. And it stores them FIFO… so it would read them FIFO.”
Buffer (which tbh i hadnt thought of) doesnt actually store the data, but the addressing… which would, again, in my head, first pull a table based on the table file (FIFO), and then if called again, would return the same list (still FIFO)…