Thank you for your answer. The view work like hell. Only thing that is
wrong in your setup is that sum(debit) and sum(credit) should be put
in the view because i want sum for all ledger_id's. The way you put it
those fields were sumarized only for the max(ledger_id). The rest is
great. I still can't believe that time of fetching has been reduced
from 14s to 390ms. I have to check everything again, but thank you!!!

Rade

--- In firebird-support@yahoogroups.com, Ali Gökçen <ali_gokcen@...>
wrote:

>
> 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
> >
>