Menu
I have a MySQL table with following data (sample);
UserId - DeviceId - StartDate - EndDate
------------------------------------------------------
1001 D119822 11/27/2011 12/02/2011
1001 D198726 11/27/2011 12/02/2011
1001 D552833 11/27/2011 12/02/2011
1001 D980993 11/27/2011 12/02/2011
I have searched for some kind of row_num or ordinal_position thing, but found nothing.
How could I get the latest record from the above table without adding a serial/auto increment number column?
777
We can use the ORDER BY statement and LIMT clause to extract the last data. The basic idea is to sort the sort the table in descending order and then we will limit the number of rows to 1. In this way, we will get the output as the last row of the table.
SELECT * FROM ( SELECT * FROM yourTableName ORDER BY id DESC LIMIT 10 )Var1 ORDER BY id ASC; Let us now implement the above query. mysql> SELECT * FROM ( -> SELECT * FROM Last10RecordsDemo ORDER BY id DESC LIMIT 10 -> )Var1 -> -> ORDER BY id ASC; The following is the output that displays the last 10 records.
METHOD 1 : Using LIMIT clause in descending order As we know that LIMIT clause gives the no. of specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending.
To get the first and last record, use UNION. LIMIT is also used to get the number of records you want.
Try this query, i have tested it and it works for me.
select s.*, @rownum:=@rownum+1 as rank
from your_table s, (SELECT @rownum:=0) r
order by rank DESC
LIMIT 1;
"last" can be alphabetical, time, value etc. You need to define that in a simply query:
SELECT *
FROM Mytable
ORDER BY (some criteria that defines "last") DESC
LIMIT 1;
Ok, so the answer to this question is simply NO. MySQL does not provide you with a built in feature that let you keep track of the order in which data has been inserted.
You could add a column that auto increments (unique id) when a row is inserted, or you could add a time stamp column to keep track of the time at which the row was inserted.
If you're not allowed to manipulate the table then maybe you could add a new table to store the latest added row of data (but this is just a bad solution really).
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
