--- In firebird-support@yahoogroups.com, Svein Erling Tysvær wrote:

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

Sorry, just noticed that you just wanted the price for the last
ledger_id and the sums for the entire item. Assuming that ledger_id is
unique for each item, this is the SQL I would try:

SELECT J.ITEM, J.PRICE, (SELECT SUM(J3.DEBIT) FROM JOURNAL J3 WHERE
J3.ITEM = J.ITEM), (SELECT SUM(J4.CREDIT) FROM JOURNAL J4 WHERE
J4.ITEM = J.ITEM)
FROM JOURNAL J
WHERE NOT EXISTS(SELECT * FROM JOURNAL J2
WHERE J2.ITEM = J.ITEM AND J2.LEDGER_ID > J.LEDGER_ID)

No group by or anything complex, just a few subselects. Of course, SUM
is a calculation that by itself may be a bit time consuming and you
really need an index where the first field of that index is ITEM.

HTH,
Set