--- In firebird-support@yahoogroups.com, "radevojvodic" 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

Something like

SELECT R.ITEM, SUM(R.DEBIT), SUM(R.CREDIT)
FROM RADETABLE R
WHERE NOT EXISTS(SELECT * FROM RADETABLE R2
WHERE R2.ITEM = R.ITEM AND R2.LEDGER_ID > R.LEDGER_ID)
GROUP BY R.ITEM

I hope you do have indexes on ITEM (that's minimum) and LEDGER_ID
(more optional, but most likely desireable), or possibly a combined
index on (ITEM, LEDGER_ID)?

I think you have to go through this NOT EXISTS for every record in
RadeTable, and if it is a huge table it will be a bit slow. Though the
above mentioned indexes will help considerably.

HTH,
Set