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

thanks for the reply. what options do i have within labkey sql (as opposed to true postgresql or mssql) for creating and using stored functions like that? can one make a db function and simply passthrough from labkey into it?

If there were a known table (as opposed to say a study dataset where the name might change) you could just create a sql view in the EHR schema and use it like any other table.

Since this is a study dataset, that won't work. We do not have a way to 'passthrough' arbitrary functions to the underlying database. It's technically possible, but we'd need some sort of mechanism to specify 'safe' functions. This is not currently planned work.

hi matt,

that's what i figured. so how about this idea:

while the hard table associated with the dataset study.weights will not have a consistent name in postgres, could you do some pseudo-SQL along the lines of:

set @weight_tables = select tablename from pg_tables where schemaname='studydataset' WHERE tablename ilike '%_weights';
foreach @weight_tables
   --create a materialized view for this table
   --create some triggers that keep this view up to date


the part i dont know about is whether you can write SQL where the table name in the FROM clause is actually a variable.

You can't execute a SQL statement with a variable for a table name. However, you can create a string containing a SQL statement and then execute it. I don't know the postgres syntax.

This seems like one of those problems that might actually be in one of Joe Celko's books (but which one...)