(Class::DBI Oracle SQL) rownum and group by woes

Tyraziel has asked for the wisdom of the Perl Monks concerning the following question:

I guess this would be more along the lines of an SQLish question. I have an SQL query that looks something like: (changed for obvious reasons)

  select sum(t.amount) as total, a.id, t.id
    from accounts a, transactions t
   where a.id = t.id and rownum < ?
group by (a.id, t.id)
[download]

So I have this query getting converted to CDBI objects via sth_to_objects. I would like to be able to limit the amount of rows I get back to a certain number, but to no avail the group by messes rownum up, becuase the join will get rownum records and group by compacts them, so I essentially can get less than what my rownum states.

Is there a way I can limit the number of objects I get back using Class::DBI, am I going to have to get the whole object array back and truncate it, or is there any other way via SQL to get my desired result without the addition of a second query?

Comment on (Class::DBI Oracle SQL) rownum and group by woes Download Code

Replies are listed 'Best First'.
Re: (Class::DBI Oracle SQL) rownum and group by woes by socketdave (Curate) on Jul 15, 2005 at 15:30 UTC
Tyraziel, try this: select sum(t.amount) as total, a.id, t.id from accounts a, transactions t where a.id = t.id group by (a.id, t.id) having rownum < ? Where works before 'group by' and having works after.	[reply]
Re^2: (Class::DBI Oracle SQL) rownum and group by woes by Tyraziel (Novice) on Jul 15, 2005 at 17:36 UTC
Brilliant! Simply Brilliant! That didn't even cross my mind to put that in the having clause, and great explination on how where works before group by and having works after. I did not know that :-) So far I've asked 2 questions here on PerlMonks and got 2 great answers! PerlMonks++ and socketdave++	[reply]
Re^3: (Class::DBI Oracle SQL) rownum and group by woes by socketdave (Curate) on Jul 15, 2005 at 18:15 UTC
Glad I could help... I had to think about this one for a minute. I'm hopelessly addicted to 'limit' in MySQL.	[reply]
Re: (Class::DBI Oracle SQL) rownum and group by woes by anonymized user 468275 (Curate) on Jul 15, 2005 at 15:25 UTC
One way is to use a cursor to fetch only up to a specified number of results from the join, before sending it to perl. In a LOOP, FETCH each result and increment a counter (in SQL). If it's PL/SQL, use an EXIT WHEN statement that checks both the result of the fetch and the value of the counter. One world, one people	[reply]