At 07:57 AM 10/06/2008, you wrote:

>Thanks to all for each try, but it seams I am not clear with my
>example; so please give me a chance to try again:
>
>An sample table called MOVI is:
>
>MOVI Char(10) ----------> primary key (unique record id)
>PROC Char(10) ----------> groups key
>FECH Char(12) ----------> datetime
>DSCR Varchar(200) ------> description
>etc, etc, etc ..
>
>I have to get the whole record with the last FECH, of each group. I
>can obtain the last FECH very fast, by doing:
>
>select max("FECH") from "MOVI" group by "PROC"
>
>But ... how to get whe whole record instead of only "FECH" ???

Logically, you are asking the impossible to get this record using GROUP BY. GROUP BY sorts the intermediate output into *groups* according to the GROUP BY criteria, so expecting to be able to return all columns from a one-column grouping is illogical and the engine will return an exception if you try.

The solutions offered by various people enable you to test your full-row output and match each row's FECH and PROC values to the search predicated on subquerying the max(FECH) value for each PROC value.

You should note also that, because FECH is not unique, you will potentially get more than one row returned, especially considering the poor granularity of your FECH literal. (It's really hard to understand why you don't store this as a TIMESTAMP!)

The solution offered by Sean Leyne comes closest (logically) to returning the result you want, i.e. (with typos corrected and unnecessary double-quotes removed):

SELECT
M1.PROC... <-- he means the list of output fields you want)
FROM MOVI M1
WHERE
NOT EXISTs(
SELECT 1
FROM MOVI M2
WHERE
M2.PROC = M1.PROC
AND M2.FECH > M1.FECH)
)

(with INDIVIDUAL indexes on PROC and FECH, though - a composite index won't help here)

Any solution involving a correlated subquery will be slow. The problem here is, if you add in the NOT predicate, it will be exponentially slower.

With an index on PROC and a DESCENDING index on FECH, you should get the same result faster this way:

SELECT
M1.PROC,
m1.FECH,
etc....
FROM MOVI M1
WHERE
m1.FECH =
(select max(m2.FECH) from MOVI m2
where m2.PROC = m1.PROC)

./heLen