ORDER BY

Hi everybody,

I explain my problem:

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.

Thans all
Steve


Superstivix (BOB member since 2004-07-09)

Highlight the column you want to order by in the report, right click, and then sort by :wink:


Rich :uk: (BOB member since 2002-10-04)

Hi and welcome to Bob! :mrgreen:

You don’t need an order by.
The SQL to get, for example, the most recent event before Christmas Day would be:


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')

where a and b are aliases of the same table.

Hope that helps,
Mark

If I understand correctly then you need to open the query panel and put the sort on the Date object.

Click on the object and click the A!Z button in the toolbar. this will put Order by in your query


JaiGupta (BOB member since 2002-09-12)

No, i can’t do that !
I must do this in the designer, not in the reporter.
I need this date in order to create an other object.

But for your quick answer :wink:

Steve

P.S: I’m french so if my english is not really correct you can notice me


Superstivix (BOB member since 2004-07-09)

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.


Steve Krandel :us: (BOB member since 2002-06-25)

Yes i know that but i did not find any other solution!
I will test the mark solution on Monday.
I’ll try to find an other solution too.

Good Week end all

Steve


Superstivix (BOB member since 2004-07-09)

Hi mark and thank you,

your solution works perfectly!
I didi not find another solution to solve my problem but this solution seems to be really good.

See you soon
Steve


Superstivix (BOB member since 2004-07-09)

C’est rien mon ami.

Bon chance.
8)

Mark,

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”? :crazy_face:
Thanks
LJK

[added bbc CODE formatting around the SELECT statement - Andreas]


ljkidd9 :us: (BOB member since 2004-06-15)