Select id for max(date) in table

[Pejo]

Hi,

I have a question that many of you must have come across some time.

I have at table that contains an id, date and some other columns not relevant to the question.
What I want is to get the row with the latest date. It's easy to get the max(date) just by:

select max(date)
from table

but if I want to get the id for that table, how do I do that?


I know I can do this:

select id
from table
where date = (select max(date)
from table)

but as I have quite many more tables in this select that I have to join it seems that the select will be very time-consuming if I have to have a subselect that has the same tables and joins that I have in the main select.


All suggestions are welcome!

/PeJo

 

[Turkbear]

Please clarify:
Do you mean that for all the joined tables you need to determine the max(date) in each as a criteria?
If so you may need to do something like:

Select a.id aid,b.id bid,c.id cid,d.id did
from a,b,c,d
where
a.date=(select max date from a) and
b.date = (select max(date) from b) and
c.date = (select max(date) from c) and
d.date = (select max(date) from d);

If the date field is indexed in each table, it should be fast enough...
Or you could try a union of the separate selects..