show 3 most recent weights for each subject?

LabKey Support Forum (Inactive)
show 3 most recent weights for each subject? trent  2011-06-05 23:42
Status: Closed
 
I think the built in group_concat function is no good, as it wont obey the limit clause (from when I tested anyways). Using an aggregate method, easy solution would be just to make your own function.

i.e. select subject_id, get_top_3(subject_id) as recent_entries
from table
group by subject_id.

where get_top_3 is a function you make

pseudo function:

for i in (select weight from weights where subject_id = p_subject_id order by date desc limit 3) LOOP
l_weights := l_weights || ', ' || i.weight;
END LOOP;

return weights;

sort of thing.

edit: found this function looks like similar set up: http://archives.postgresql.org/pgsql-sql/2004-04/msg00100.php

If not an aggregate, you might like to see the following for some ideas:

http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category
http://www.bennadel.com/blog/1114-Selecting-Top-X-From-Each-Group.htm
http://www.sql-ex.ru/help/select16.php

..

Well, hopefully that gives you some ideas - someone else may be able to offer a better solution - personally, i think writing your own function is the way to go.... :)