take only one record of select

Question

I need to fetch only first record (because I need last date) of resultset, at the moment I have this resultset from this sql tring:

SELECT BCACC,FLDAT
FROM ANAGEFLF
ORDER BY FLDAT DESC

and I see this record:

A.M.T. AUTOTRASPORTI SRL        20080220
A.M.T. AUTOTRASPORTI SRL        20080123
A.M.T. AUTOTRASPORTI SRL        20070731
APOFRUIT ITALIA                 20080414
APOFRUIT ITALIA                 20080205
APOFRUIT ITALIA                 20071210
APOFRUIT ITALIA                 20070917
APOFRUIT ITALIA                 20070907

now I need to take only one record (first) for every BCACC, I would take this resultset:

A.M.T. AUTOTRASPORTI SRL        20080220
APOFRUIT ITALIA                 20080414

I've just try group it for BCACC but I receive an sql error, I'm workin on DB2 ibmI

Answer 1

Assuming they are the only fields involved, you can just do a GROUP BY.

SELECT
  BCACC,
  MAX(FLDAT) AS FLDAT
FROM
  ANAGEFLF
GROUP BY
  BCACC

If, however, you have other fields that you need, then you just join this back of the table as a sub-query...

SELECT
  ANAGEFLF.*
FROM
  ANAGEFLF
INNER JOIN
(
  SELECT
    BCACC,
    MAX(FLDAT) AS FLDAT
  FROM
    ANAGEFLF
  GROUP BY
    BCACC
)
  AS map
    ON  map.BCACC = ANAGEFLF.BCACC
    AND map.FLDAT = ANAGEFLF.FLDAT

Answer 2

select BCACC, max(FLDAT)
from ANAGEFLF
group by BCACC