CODEWITHSUNDEEP
sqldb2
jack.cap.rooney
jack.cap.rooney

Reputation: 1306

take only one record of select

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

Upvotes: 6

Views: 177

Answers (3)

AngocA
AngocA

Reputation: 7693

The answer for the first part of your question "I need to fetch only first record of resultset" is:

SELECT BCACC,FLDAT
FROM ANAGEFLF
ORDER BY FLDAT DESC
FETCH FIRST 1 ROW ONLY

But the global question was another, about how to fix a query to only retrieve the correct values.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86798

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

Upvotes: 3

Christopher Weiss
Christopher Weiss

Reputation: 755

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

Upvotes: 1

Related Questions