I’ve got an SQL table in which i have some dates.
I need to get the highest date which is before another date.
The solution i’m thinking about is to get all the dates that are before my reference date and after to order those dates from the highest to the lowest. Once i did that i just have to get the first result i get whith TOP 1.
The big problem is that i don’t how to set an “order by” condition directly in an object.
If someone has the solution, he would be kind to give me the answer.
Although it will perform slowly, you need the subquery mark mentioned. You should be able to put it in the Where portion of your object. Just understand the limitations of putting a “Where” on an object. This can often lead to problems when using it in conjunction with other objects.
I’ve been trying to put this type of SQl statement into an designer object for awhile (newbie on Designer)
select * from your_table a
where a.event_date =
(select max(b.event_date) from your_table b where a.id = b.id and b.event_date < '25-DEC-2003')
Obviously, I don’t understand how this fits into the object select statement and where clause (and don’t know how you alias the tables in Designer – i don’t really have to create a whole alias table just to perform this, do I?)
Can you break it down into “Objects for dummies”?
Thanks
LJK
[added bbc CODE formatting around the SELECT statement - Andreas]