SQL - How to get the latest entry between two different tables? (The timestamp is not stored)

Question

I'm working on an ongoing project in which I'm dealing with quotes and the database structure is not well organized and I can't make too many changes to the database schema to fix it.

In the database:

• Two types of quotes and saved in two different tables.
• Assume regular_quotes and premium_quotes are two tables.
• The situation is that there is no table that stores date/time, there is no column related to created_at, and there is no backup columnstoragedate/ time
• I have to check which table has the latest entry from these two tables.

Table 1 Regular quotes

id | name | quoteno | status  | .....|
------------------------------------------
1  | name1| RQ-909099 | pending  | .....|
2  | name2| RQ-800099 | pending  | .....|
3  | name3| RQ-965099 | approved | .....|

Table 2 premium_quotes

id | name | quoteno | status  | .....|
------------------------------------------
1  | name1| PQ-209099 | pending  | .....|
2  | name2| PQ-300099 | pending  | .....|
3  | name3| PQ-965099 | pending  | .....|

What I did was:

• Created a new tablerecent_quote_meta
• has the following columns: id, quote_id, quote_type, created_at, updated_at, deleted_at
• Whenever a qny quote is created, I store it in recent_quote_meta and use quote_id
• From this I am getting the latest quote_id and quote_type and based on this I am getting and displaying the quotes accordingly

But the situation is, they don't want me to make any changes to the database. And I don't think that can be done in the current scenario.

Is there a way to get the latest quotes for these tables? It can be regular_quotes or premium_quotes

Thanks!

Answer 1

Without any timestamp column, there is actually no way to determine which of the two tables has the latest quote entry. There are some tricks we can try, but your situation will determine whether they are trustworthy.

One way is that you can check if the IDs are assigned in order, and since you know the latest ID in both tables, you can compare them to determine which table has the latest quote. For example, if the latest id in the regular_quotes table is 3, and the latest id in the regular_quotes table > The premium_quotes table is 5, and it can be concluded that the premium_quotes table has the latest quotes.

SELECT MAX(id) AS latest_regular_quotes_id FROM regular_quotes;


SELECT MAX(id) AS latest_premium_quotes_id FROM premium_quotes;

These will return the highest ID value in each table. You can compare these two values ​​to determine which table has the latest quotes, with id having the highest value.

Ideally, you may want to tell an administrator to resolve this issue.

Hope this is useful to you.