Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

max(id) of id column

user1014019Apr 21 2009 — edited Apr 22 2009
Dear Sir

I have 100 master tables in oracle.In each master there is a column name ID,code.There is a sequence in each master table which refers to ID Column.If I wanted to find out max(id) of one master table.I am writing the script as
sql> select max(id) from mst_stddes;
max(id)
---------
100

1 row selected.

I wanted to find out the max(id) of every master table.There is no data dictionay view available in Oracle which would help us to get the max(id) of every master table?

What is the possible way of retrieving the max(id) of 100 master table in a query?
Is there any way we can retrieve the max(id) of every record

One method is
e.g:-
select 'table_name',max(id) from table_name
union
select 'table_name1',max(id) from table_name1
union
select 'table_name2',max(id) from table_name2
union
select 'table_name3',max(id) from table_name3;

Here table_name,table_name1,table_name2,table_name3 are the master tables


Can you please suggest me the optimal way to find the max(id) of 100 master tables in a query in one shot?


Appreciate your help on the above.?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 20 2009
Added on Apr 21 2009
5 comments
2,497 views