Hi,
first, you need an index on (item,ledger_id[,id]).

create view jmax(item_id,ledger_id)
select item,max(ledger_id) from journal
group by item

this view will give you max ledger_idies using the index.

then your query:

select jm.item_id,jm.ledger_id,
sum(j.debit), sum(j.credit), max(j.price)
from jmax jm
join journal j on j.item=jm.item_id and j.ledger_id=jm.ledger_id
group by 1,2

Regards.

Ali

--- In firebird-support@yahoogroups.com, "radevojvodic" <vrade@...>
wrote:

>
> Hi All,
>
> I have a table like this
>
> ID, LEDGER_ID, PRICE, DEBIT, CREDIT, ITEM
>
> I have to find Price where LEDGER_ID = MAX(LEDGER_ID) for each ITEM
> and at the same time SUM(DEBIT), Sum(CREDIT) for that item. I have
> tried to Join the table (lets name it journal) to itself and to use
> having clause to get it to work. It does work but it's very slow.

Is

> there anyone that can help me to optimize the query.
>
> Rade
>