Hi Svein,

This is a basicly a Journal of items in a warehouse.

MAG_ID stand for warehouse ID, ROBA_ID stand for Item ID, CIJENA
stands for price. SDOK table is a Journal table. For each change of
item new SDOK_DNROB is increased. after a few changes sdok would
look like this

MAG_ID ROBA_ID CIJENA SDOK_DNROB KOR_ULAZ KOR_IZLAZ
11 1 1,3 1 5 0
11 1 1,4 2 0 1
11 1 2,9 3 0 1
11 2 2 1 2 0
11 2 2 2 0 1
12 1 1,5 1 5 0
12 1 1,6 2 etc. 0 1

I want to find for each item in selected MAG_ID record in which
SDOK_DNROB has maximum value, read the CIJENA field for that record
and simultaneously to have SUM(KOR_ULAZ-KOR_IZLAZ). This query
should give: current price (last price = CIJENA) and current
quantity (SUM(KOR_ULAZ-KOR_IZLAZ)) for each item (roba_ID) in each
warehouse (MAG_ID).
Inner join SDOK with itself and HAVING clause does this job. I tried
it with subselect statements and it is much slower than this one.

Rade

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

>
> --- In firebird-support@yahoogroups.com, "radevojvodic" wrote:
> > Hi all,
> >
> > Ihave a select statement that looks like this. Can someone help

me

> > on configuring indexes (and PLAN statement) to boost

performance.

>
> Unfortunately, a select statement isn't enough to tell which

indexes

> to create, it all depends on your data, e.g. an index on mag_id

would

> be good if it was unique, whereas it would be horrible if 90% of

the

> records contained the value 11.
>
> But let us look at what you are actually trying to do, maybe we

can

> improve your SQL.
>
> > SELECT S1.MAG_ID, S1.ROBA_ID, MAX( S2.SDOK_DNROB ),
> > S1.CIJENA, SUM( S2.KOR_ULAZ - S2.KOR_IZLAZ ),
> > S1.SDOK_DNROB,
> > R.roba_naziv
> > FROM SDOK S1
> > INNER JOIN SDOK S2 ON (S1.MAG_ID = S2.MAG_ID) AND
> > (S1.ROBA_ID = S2.ROBA_ID)
> > INNER JOIN ROBA R ON S2.ROBA_ID = R.ROBA_ID
> > WHERE ( ( (S1.SDOK_DNROB > 0 ) and
> > (S2.SDOK_DNROB > 0 ) ) and
> > S1.mag_id = 11)
> > GROUP BY S1.MAG_ID, S1.ROBA_ID, S1.SDOK_DNROB, S1.CIJENA,
> > R.roba_naziv
> > HAVING ( (S1.SDOK_DNROB = MAX(S2.SDOK_DNROB)) )
> > Order By S1.MAG_ID, S1.ROBA_ID, S1.SDOK_DNROB
>
> Well, I'm confused by your HAVING clause. Is this equivalent to:
>
> SELECT DISTINCT S1.MAG_ID, S1.ROBA_ID, S1.CIJENA,
> (select SUM(S2.KOR_ULAZ - S2.KOR_IZLAZ)
> from SDOK S2
> where S2.MAG_ID = 11 AND S1.ROBA_ID = S2.ROBA_ID,
> S1.SDOK_DNROB, R.roba_naziv
> FROM SDOK S1
> INNER JOIN ROBA R ON S2.ROBA_ID = R.ROBA_ID
> WHERE S1.SDOK_DNROB > 0 and S1.mag_id = 11
> and not exists(select *
> from SDOK S3
> where S3.MAG_ID = 11 AND S1.ROBA_ID = S3.ROBA_ID

and

> S3.SDOK_DNROB > S1.SDOK_DNROB)
> Order By S1.MAG_ID, S1.ROBA_ID, S1.SDOK_DNROB
>
> OK, this may well be a different select, so I'll stop guessing

now.

> Tell us about your data (which fields are selective) and what you

are

> trying to do with your select (either it is confusing, or it is

just

> yet another proof that I still have a lot to learn about selects).
>
> HTH,
> Set