You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by "Peter S. Hamlen" <ph...@mail.com> on 2003/03/25 18:24:36 UTC
RE: getting id of MAX entry (try ORDER BY)
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
On Mon, 2003-03-24 at 13:08, Jim Schneider wrote:
> Perhaps using Group BY would work.
>
> You could also do something like,
>
> Select event_id from table where event_date = (select max(event_date)
> from table).
>
> Although I've never used it, and I'm not sure whether this is standard,
> but there is a "Limit" keyword in some dbs.
>
> Jim
>
> -----Original Message-----
> From: Dan Allen [mailto:dan@mojavelinux.com]
> Sent: Monday, March 24, 2003 11:00 AM
> To: torque-user@db.apache.org
> Subject: getting id of MAX entry
>
> This is sort of more SQL related, but how would you find the id of
> the record that is returned as the MAX() of another column. For
> instance, just say I had events that occured at all different times.
> I wanted to find the most recent event. I would do
>
> select MAX(date) from table;
>
> but now I want something like
>
> select event_id, MAX(date) from table;
>
> naturally that gives a mix error, so what would I do?
>
> I could always query again with the result of the MAX(date) used the
> next query, but I can't seem to use MAX in the where clause either.
>
> Dan
>
> --
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> Daniel Allen, <da...@mojavelinux.com>
> http://www.mojavelinux.com/
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> Chaos reigns within.
> Reflect, repent, and reboot. Order shall return.
> - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
Re: getting id of MAX entry (try ORDER BY)
Posted by Dan Allen <da...@mojavelinux.com>.
Actually, I figured it out and it makes TOTAL sense. The limit and
offset are applied after the query is executed (unless the database
has the ability to take advantage of this inside the database API
and then it does so) and before the object collection is created.
That is perfect for what I need. I already know that a database
does not suffer from returning all the rows versus only a subset if
kept inside the native database result set...it is only when
capturing a row out of the native result set that costs money and
the torque API handles this perfectly. So now I am no longer afraid
of the setLimit() and setOffset() since I know that they are applied
between the query and the creation of the collection of
objects...PERFECT!
Dan
--
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Daniel Allen, <da...@mojavelinux.com>
http://www.mojavelinux.com/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
umm... i guess this is my signature. 8-}
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Re: getting id of MAX entry (try ORDER BY)
Posted by "Peter S. Hamlen" <ph...@mail.com>.
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, <da...@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
>
Re: getting id of MAX entry (try ORDER BY)
Posted by Dan Allen <da...@mojavelinux.com>.
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, <da...@mojavelinux.com>
http://www.mojavelinux.com/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'One Microsoft Way' is more than just an address.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -