getting latest 100 rows inserted into table

[Gopi Chand Maddula]

Hi Java Ranch,

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.


Thanks in Advance.

[Paul Sturrock]

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

Not sure I understand why this is an issue?

And another problem in my tables is i don't have any auto increment column in my tables to use ORDER BY clause.

So you have nothing to order this by? In which case what you are trying can't work, unless you add a timestamp or sequential key to your table.

[Deepak Bala]

SELECT * FROM Person ORDER BY PersonID DESC LIMIT 100

Ordering by person ID does not guarantee the output that you desire. Use a timestamp like Paul suggested.

[Gopi Chand Maddula]

Hey Paul,

Thanks for your reply.

Not sure I understand why this is an issue?

Its not an issue its my requirement.

So you have nothing to order this by? In which case what you are trying can't work, unless you add a timestamp or sequential key to your table.

Yes i do have but in some tables i have ID to order and in some timestamp and in some tables neither of them are available. So i am planning to get the details irrespective of table's columns.

I am designing an which will just have list of tables in the database. So, from that interface i am trying to get the details.

[Paul Sturrock]

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.

[Gopi Chand Maddula]

Paul Sturrock wrote:But changing the schema if you need to is the easiest way.

Off course its a good idea Paul, But changing the schema of the tables for this requirement is not possible for me and i have to go through lot of approval procedure for the modification.

Can we have the count instead for checking the row count? I am working that way to get the results.

[Paul Sturrock]

No.

[Gopi Chand Maddula]

Paul Sturrock wrote:No.

I think No would not be the answer or suggestion Paul. Anyway thanks for your help throughout the post, 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.

[Deepak Bala]

Gopi Chand Maddula wrote:

Paul Sturrock wrote:No.

I think No would not be the answer or suggestion Paul. Anyway thanks for your help throughout the post, 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.

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.

[Gopi Chand Maddula]

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.

[Paul Sturrock]

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.

[Jan Cumps]

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.

[Jan Cumps]

Ah, 3 similar answers entered at the same time.

[Jan Cumps]

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.

[Gopi Chand Maddula]

Jan Cumps wrote:

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???

Please let me know if i miss anything...!

[Jan Cumps]

Is there any big difference between the last and the latest for my scenario???

Please let me know if i miss anything...!

Yes. you are looking for the latest records that are entered into a table,
but your quey is retrieving 100 records of an unsorted (i.e. not predictable) set of records.

Your query does not have an order by. That means that the database can return the records in any order it wants.
SQL does not prescribe a return order if you don't tell it how to sort the results.

And the 100 last records of an unsorted set can be: any 100 records of your table. No guarantees here that they are the 100 last records of a table..

[Ronald Djunaedi]

im agree with most of the posts in here,
Changing your table will be the fastest way, add the timestamp column, so everytime you want to get 100 last inserted just do the query

Select top 100 * from my_table order by my_timestamp DESC

in the Sql Server,for others, will be similar though not in the exact same shape...

[Jan Cumps]

Welcome to the Ranch, Ronald.

[Ronald Djunaedi]

Jan Cumps wrote:Welcome to the Ranch, Ronald.

Thanks Jan