To Select only the latest first record from db2 table

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

Hi,

Can anyone give me a query to select only the latest record from the db2 table and only one record.

GuyC · Posted: Tue May 31, 2011 8:09 pm

order by ? fetch first ?

santosh ks · New User Joined: 27 May 2011 Posts: 16 Location: india

Can you be more specific with your question? How can we give the query without specifying any data.

Before posting it directly here, do google it. You will find many solutions.
Are you so lazy that even this should be done by us.

Anyway you can use a query of this kind

SELECT * FROM table
where col = (SELECT MAX(col) FROM table)

Here 'col' can be one of the column of the table which increments each and every time a new record is added into the table.[/quote]

dbzTHEdinosauer · Posted: Tue May 31, 2011 8:49 pm

gylbharat · Posted: Wed Jun 01, 2011 11:37 am

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

Hi,

Here is the query that i have coded but this will give sql -811, I want to get only the latest only one record based on the time stamp.

Can you tell me what else do I need to add to this query to fetch only one record.

SELECT ESCHEAT_IND
INTO :WHS-20-ESCHEAT-IND
FROM GIWHS$01.WHS_20_EXTRACT
WHERE DRAFT_OFFICE = WS-DRAFT-OFFICE
AND DRAFT_LOGICAL_NO = WS-DRAFT-NUM
ORDER BY ACTIVITY_TS DESC

Thanks.

singhju · New User Joined: 01 Dec 2010 Posts: 25 Location: Gurgaon

You need to specify FETCH FIRST ROW ONLY after order by clause.

santosh ks · New User Joined: 27 May 2011 Posts: 16 Location: india

This query will fetch multiple rows from the table since there are multiple rows present in the talbe.

You can use this as cursor and fetch only the first record, then it is possible to fetch only one record.

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

Using Cursor is a good option but my customer wants me to fetch without using cursor in a single query in the program. Is there any ways to do this ?

gylbharat · Posted: Wed Jun 01, 2011 2:40 pm

Modify the query

SELECT ESCHEAT_IND
INTO :WHS-20-ESCHEAT-IND
FROM GIWHS$01.WHS_20_EXTRACT
WHERE DRAFT_OFFICE = WS-DRAFT-OFFICE
AND DRAFT_LOGICAL_NO = WS-DRAFT-NUM
ORDER BY ACTIVITY_TS DESC
Fetch first 1 row only ;

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

Using this query I compiled but I am getting an error as '"ORDER BY" CLAUSE NOT PERMITTED'.

I removed the host variables and ran in QMF this same query is working fine here.

dbzTHEdinosauer · Posted: Wed Jun 01, 2011 4:26 pm

sounds as if you are developing under version 7
and your qmf is version 8.

pays to know your environment.

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

i checked now, both the versions are 7.

dbzTHEdinosauer · Posted: Wed Jun 01, 2011 4:35 pm

GuyC · Posted: Wed Jun 01, 2011 5:51 pm

if ACTIVITY_TS is unique (at least within DRAFT_OFFICE ,DRAFT_LOGICAL_NO )

SELECT ESCHEAT_IND
INTO :WHS-20-ESCHEAT-IND
FROM GIWHS$01.WHS_20_EXTRACT A
WHERE DRAFT_OFFICE = :WS-DRAFT-OFFICE
AND DRAFT_LOGICAL_NO = :WS-DRAFT-NUM
and ACTIVITY_TS =
(select max(ACTIVITY_TS) from GIWHS$01.WHS_20_EXTRACT B
where A.DRAFT_OFFICE = B.DRAFT_OFFICE
and a.DRAFT_LOGICAL_NO = b.DRAFT_LOGICAL_NO )

"ORDER BY" CLAUSE NOT PERMITTED probably has something to do with the precompiler version/settings.

Jeevankumar.Pochammala · New User Joined: 04 May 2011 Posts: 7 Location: INDIA

Hello Kumar1234,

just small modification the query given by gylbharat

SELECT ESCHEAT_IND,ACTIVITY_TS
INTO :WHS-20-ESCHEAT-IND,
:WHS-20-ACTIVITY-TS
FROM GIWHS$01.WHS_20_EXTRACT
WHERE DRAFT_OFFICE = WS-DRAFT-OFFICE
AND DRAFT_LOGICAL_NO = WS-DRAFT-NUM
ORDER BY ACTIVITY_TS DESC
Fetch first 1 row only ;

I those this query work fine

gylbharat · Posted: Mon Jun 13, 2011 1:34 pm