Re: Re: avg() AND limit

2006-12-18 Thread Dan Buettner 

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]

Re: avg() AND limit

2006-12-18 Thread Dan Buettner 

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 <[EMAIL PROTECTED]> 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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]