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.... :) |
||