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 Tue, 25 Mar 2003 17:24:36 GMT
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, <dan@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
> 



Mime
View raw message