db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Peter S. Hamlen" <pham...@mail.com>
Subject Re: getting id of MAX entry (try ORDER BY)
Date Wed, 26 Mar 2003 22:50:02 GMT
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 (phamlen@mail.com) 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, <dan@mojavelinux.com>
> http://www.mojavelinux.com/
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> 'One Microsoft Way' is more than just an address.
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
> 



Mime
View raw message