I'm sure there is ... here's one way I can think of, a two-step process but doable in 3.23 I think. Use a server-side variable and a combination of the LIMIT and OFFSET features.
SELECT id FROM items ORDER BY DATE DESC LIMIT 1 OFFSET 10 INTO @myid then SELECT AVG(cost) FROM items WHERE id >= @myid this assumes that going by date and id is valid for your situation. Hopefully you can tweak that a little if not. Also, the LIMIT/OFFSET can be used as "LIMIT 10,1" instead of "LIMIT 1 OFFSET 10". I don't know which form will be valid in 3.23, sorry! -Dan On 12/18/06, Richard Reina <[EMAIL PROTECTED]> wrote:
Dan, Thank you very much for the reply. Is there a way to do it with version 3.23.54? Dan Buettner <[EMAIL PROTECTED]> wrote: Yes, the LIMIT function affects number of rows returned, not number of rows evaluated. If you're on 4.1 or later, you could use a subselect. Assuming you have an ID field in your table, something like this: SELECT AVG(cost) FROM items WHERE id IN (SELECT id FROM items ORDER BY date DESC LIMIT 10) HTH, Dan On 12/18/06, Richard Reina wrote: > Good day all! I have query like the one below that I uses to get the average cost over the last six months. As is it works fine but, however now I want to limit it to the last 10 rows in order to get a glimpse of the most recent cost data. If I add ORDER BY date DESC limit 10 the average does not change. Does anyone know how I can achieve the desired result? > > Thanks in advance. > > SELECT AVG(cost) > FROM items > WHERE UNIX_TIMESTAMP(date) >= (UNIX_TIMESTAMP(CURDATE() - 15724800) > > > > > > Your beliefs become your thoughts. Your thoughts become your words. Your words become your actions. Your actions become your habits. Your habits become your values. Your values become your destiny. -- Mahatma Gandhi > Your beliefs become your thoughts. Your thoughts become your words. Your words become your actions. Your actions become your habits. Your habits become your values. Your values become your destiny. -- Mahatma Gandhi
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
