I have a Requirement to display the latest 100 rows inserted into the table. In my database i have nearly 30 tables and every table will have huge amount of data in it. But every time i want to refer i need to get the latest 100 records inserted. By the way i am using MySQL as my database.
i have tried the following Query for getting the results.
SELECT * FROM Person ORDER BY PersonID DESC LIMIT 100
But in my java program i am just passing the table name as parameter to the query to get the latest rows of that particular table. And another problem in my tables is i don't have any auto increment column in my tables to use ORDER BY clause.
So, Please some please help me finding out the solution for writing the query please.
Fair enough. Like I say, without the data in the table (i.e. a sequential key or a time stamp) you can't do this with simple SQL. An overly complex way of working round this (if you can't change the schema) would be to use an insert trigger, log the key of the inserted row with a time stamp in another audit table and query that. But changing the schema if you need to is the easiest way.
Deepak Bala wrote:
You do of course realize that is not a valid solution ? "select *" in the second query does not order by any column. If you are able to obtain the desired result, it is purely by coincidence.
Offcourse i do realise Deepak.
But my requirement is just getting the last 100 rows of the each table and thats it. If i should have to get the results in order then i should go for Paul's Suggestion. But its far away from my reach and i cant afford that time as well for the requirement.
I think No would not be the answer or suggestion Paul.
No, what I say is the correct answer to your question. You may get away with this based on how the database stores the rows, though this is coincidental (as Deepak notes). You can change the behaviour or some databases to enforce a physical row order in in the table itself, for example SQL Server supports clustered indexes which (if used) means you know the row order == the order rows were inserted. I don't think MySQL has the same capabilities, either way its typically only ever used to work around missing logic that should be in the schema itself.
Gopi Chand Maddula wrote:...but i got the result for the requirement
Here is the simple query i tried for the requirement.
count = select count(*) from table_name;
select * from table_name LIMIT count-100,100
i got the result by using count. It simple returns the last 100 records in the table provided.
Thanks alot guys.
That does not work. And if it does, that is by accident.
SQL does not guarantee a specific return sequence if you do not use an order by (or group by) clause.
And LIMIT does not sort, it only restricts.
Gopi Chand Maddula wrote:...But my requirement is just getting the last 100 rows of the each table and thats it.
That is different than your original post:
I have a Requirement to display the latest 100 rows inserted into the table.
Anyway i don't realize any difference between both. The last records inserted will be the latest record inserted, Right?
The order bothers when we are getting the latest 10 or 15, if we can't confirm that the last inserted rows are the latest records.
As i was searching for latest 100 i thought the last 100 would resolve my problem and meet the requirement.
Is there any big difference between the last and the latest for my scenario???