ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nathan Maves <Nathan.Ma...@Sun.COM>
Subject Re: OT sql problem
Date Fri, 29 Apr 2005 22:20:19 GMT
hmm.... let me try and clarify what i need.

Here is the table with the data :

  METRIC_ID     FREQUENCY     PERIOD                 ACTUAL     TARGET
  ------------  ------------  ---------------------  ---------  ---------
  1             1             2005-02-01 00:00:00.0  3          1
  1             1             2005-03-01 00:00:00.0  3          1
  1             1             2005-04-01 00:00:00.0  1          1
  1             2             2005-03-01 00:00:00.0  2          2
  1             2             2005-04-29 11:34:31.0  45         34

I want the query to return the following rows :
  1             1             2005-03-01 00:00:00.0  3          1
  1             1             2005-04-01 00:00:00.0  1          1
  1             2             2005-03-01 00:00:00.0  2          2
  1             2             2005-04-29 11:34:31.0  45         34


Notice that I want the results grouped by frequency.  I also only want 
the two most recent rows based on the period column.

Nathan

On Apr 29, 2005, at 3:54 PM, Larry Meadors wrote:

> I presume you mean without using the queryForList(id, parms, skip, 
> max) call in iBATIS. ;-)
>
>  Using that it is a snap:
>
>  List topNList = queryForList("Customer.getTransactions", parms, 0, n);
>
>  Without that, depending on the database, it can be done.
>
>  SQL server has a "top (n)" syntax that you can use, and i think 
> Oracle has a rowid that you can use in a nested select (i.e., select 
> blah from (select ... ) where rowid <= 100 to get the top 100).
>
>  In a db with stored procedures it would be fairly straightforward to 
> do, too.
>
>  Larry
>
>
> On 4/29/05, Nathan Maves <Nathan.Maves@sun.com> wrote:
>> transactions for your customers.
>>
>> Is there any way in one query to pull back the last n number of
>> transactions for all customers?
>>
>> This is only a hypothetical instance.... Mine is much more difficult 
>> :)
>>
>> Nathan
>>


Mime
View raw message