aspose file tools*
The moose likes JDBC and the fly likes getting latest 100 rows inserted into table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "getting latest 100 rows inserted into table " Watch "getting latest 100 rows inserted into table " New topic
Author

getting latest 100 rows inserted into table

Gopi Chand Maddula
Ranch Hand

Joined: May 11, 2010
Posts: 40

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
Bartender

Joined: Apr 14, 2004
Posts: 10336


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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Deepak Bala
Bartender

Joined: Feb 24, 2006
Posts: 6661
    
    5

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.


SCJP 6 articles - SCJP 5/6 mock exams - More SCJP Mocks
Gopi Chand Maddula
Ranch Hand

Joined: May 11, 2010
Posts: 40

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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: May 11, 2010
Posts: 40

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
Bartender

Joined: Apr 14, 2004
Posts: 10336

No.
Gopi Chand Maddula
Ranch Hand

Joined: May 11, 2010
Posts: 40


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
Bartender

Joined: Feb 24, 2006
Posts: 6661
    
    5

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
Ranch Hand

Joined: May 11, 2010
Posts: 40

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
Bartender

Joined: Apr 14, 2004
Posts: 10336


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
Bartender

Joined: Dec 20, 2006
Posts: 2499
    
    8

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.




OCUP UML fundamental and ITIL foundation
youtube channel
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2499
    
    8

Ah, 3 similar answers entered at the same time.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2499
    
    8

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
Ranch Hand

Joined: May 11, 2010
Posts: 40

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
Bartender

Joined: Dec 20, 2006
Posts: 2499
    
    8

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
Greenhorn

Joined: Jan 06, 2011
Posts: 5

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
Bartender

Joined: Dec 20, 2006
Posts: 2499
    
    8

Welcome to the Ranch, Ronald.
Ronald Djunaedi
Greenhorn

Joined: Jan 06, 2011
Posts: 5

Jan Cumps wrote:Welcome to the Ranch, Ronald.


Thanks Jan
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: getting latest 100 rows inserted into table