[thelist] get avg of last 3 records in SQL

Jason Bauer jbauer at chimesnet.com
Thu Nov 7 13:35:01 CST 2002


> how can i get the average of the last 3 records entered by a particlar
user? or even just the last 3 > records and do the math elsewhere.
>
> idea along the lines of
> select avg(steps) as steps from tblSteps where user='100' and  record=the
last 3

Its pretty easy if you have a field in the record that tells when that
record was entered compared to the other records. If you have an update_date
field, this works:

select avg(steps) as avg_step from
(select steps from tblSteps where user='100' and rownum <= 3
 order by update_date desc)

(different SQLs may do the rownum thing differently, this is Oracle.
Basically, you just want 3 rows in the subquery)

Its the same idea if you have a incremental id field:

select avg(steps) as avg_step from
(select steps from tblSteps where user='100' and rownum <= 3
 order by id desc)

Now, if there isn't a field in the record to determine when it was entered,
I'm stumped...

---
Jason Bauer
jbauer at chimesnet.com
Programmer/Analyst
Chimes, Inc.





More information about the thelist mailing list