Well, if you're really after speed, then one option is to use the
Village api directly. There's an example down below. If you're really
interested in the SQL to get back just one row, it's a little cumbersome
- there's no guarantee that it's just one event - multiple events can
have the same date.
Nonetheless, the SQL looks like:
select * from event where event_date = (select max(event_date) from
event);
You'll have to execute this query directly using BasePeer.executeQuery.
The code looks like:
String query = "select eventid from event where event_date=(select
max(event_Date) from event)";
List events = BasePeer.executeQuery(query, false, conn);
System.out.println("Found "+events.size());
// Now we iterate over the list
for (int i=start; i < end && i < events.size(); i++)
{
Record event = (Record)events.get(i);
// You can get the number of the eventId by calling
int eventId = rec.getValue(1).asInt();
}
4) I suppose you could probably get away with doing a
Criteria.CUSTOM...
Criteria crit= new Criteria();
crit.add(Event.EVENT_DATE, (Object)"(select max(event_date) from
event)", Criteria.CUSTOM);
I haven't tried that, it might not work.
Hope this helps.
-Peter
On Tue, 2003-03-25 at 19:03, Dan Allen wrote:
>
> Peter S. Hamlen ([EMAIL PROTECTED]) wrote:
>
> > If you're really interested in the eventid with the max date, then there
> > is a simple SQL trick to solve it:
> >
> > select eventid from event order by event_date desc;
> >
> > Then grab the first eventid - that's your max. If your problem was
> > trickier - say you wanted the event id of the max date before Jan 15,
> > 2003, then it would be:
> >
> > select eventid from event where eventdate < '01/16/2003' order by
> > event_date desc;
> >
> > This kind of trick (taking only the first record returned and using
> > ORDER BY to make sure you get the max or min) is useful in lots of
> > places.
> >
> > -Peter
>
> Your point is well taken and definitely something I considered, but
> my issue is with speed. If I select everything then it would seem
> much slower than actually doing a query on one record (especially in
> torque since it spends time populating Java objects with the
> results). I have stayed away from Limit clauses since they are
> non-ansi SQL. I am very curious to know how one accomplishes
> efficiency with SQL without Limit statements.
>
> dan
>
> --
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> Daniel Allen, <[EMAIL PROTECTED]>
> http://www.mojavelinux.com/
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> 'One Microsoft Way' is more than just an address.
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
