Top-N query

(David Schoen) #1

Is there a generic way to do a Top-N style query with MatrixDAL?


I can't find a function for it and was wondering if someone knows where it's hiding.



The MatrixDAL::executePdoOne() function just returns a single value not a single row.



I'm working with Oracle at the moment but don't want to rule out this code running on PostgreSQL.



My solution for Oracle (possibly not THE solution for Oracle) is to wrap the statement in "SELECT * FROM ( <statement here> ) WHERE ROWNUM <= N", but I'm pretty sure this doesn't work in anything else.



Any ideas?

(Greg Sherwood) #2

Because PostgreSQL and Oracle have different ways of limited queries, you need to a particular Matrix DB helper function. Something like:


require_once SQ_FUDGE_PATH.'/db_extras/db_extras.inc';

$sql = "SELECT column FROM table WHERE value = 10 ORDER BY value DESC";

$sql = db_extras_modify_limit_clause($sql, MatrixDAL::getDbType(), 10);



Check out the fudge/db_extras/db_extra.inc file for function arguments.

(David Schoen) #3

Exactly what I was looking for, just didn't know where to find it.


Thanks!