|
View previous topic :: View next topic
|
| Author |
Message |
annu zacson
New User
Joined: 19 Aug 2008
Posts: 15
Location: Cochin
|
|
|
|
Hi,
I have a query to retrieve the card members from a table in the sorted order as specified below. I would like to re-iterate the query so that the SQL returns only the first 2 rows of distinct card accounts.
The query As is :
| Code: |
SELECT
ACCT_NO,
CM_SUPP_NO,
HIST_UPD_DT_TM_DA
FROM C0B.T3333 A
WHERE ISO_CTRY_ALPH3_CD = 'NZD'
AND CARD_ACCT_NO IN ( SELECT DISTINCT CARD_ACCT_NO
FROM C0B.T3333 B
WHERE
( CB_ACC_STAT_CD = 'E' OR
CAIS_STATUS_CD = 'F' OR
RGSTRY_DA_FLAG_CD = 'E' ) AND
DAY (HIST_UPD_DT_TM_DA) = 17 AND
MONTH(HIST_UPD_DT_TM_DA) = 11 AND
YEAR (HIST_UPD_DT_TM_DA) = 2011 AND
A.UPD_DT_TM_DA = B.UPD_DT_TM_DA)
ORDER BY CARD_ACCT_NO,HIST_UPD_DT_TM_DA DESC
; |
Result:
| Code: |
Card1 E updt TS -- row 1
Card1 E updt TS -- row 2
card1 E updt TS -- row 3
Card2 E updt TS -- row 4
Card2 E updt TS -- row 5
Card2 E updt TS -- row 6 |
Result required after the query change:
| Code: |
Card1 E updt TS -- row 1
Card1 E updt TS -- row 2
Card2 E updt TS -- row 4
Card2 E updt TS -- row 5
|
Could you please help to write a query so that the result will get populated as mentioned above?
Regards,
Annu |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
what the following query does is :
1) Get all distinct Card_acct_no with some flags that are updated on a certain day => Table B2
2) for those cards get the latest two updated rows in 'NZD'
| Code: |
select a2.* from
( SELECT CARD_ACCT_NO
FROM C0B.T3333 B
WHERE ( CB_ACC_STAT_CD = 'E' OR
CAIS_STATUS_CD = 'F' OR
RGSTRY_DA_FLAG_CD = 'E' )
AND hIST_UPD_DT_TM_DA between '2011-11-17-00.00.00.000000' and '2011-11-17-23.59.99.999999'
group by card_acct_no) B2
, table (
SELECT ACCT_NO, CM_SUPP_NO, HIST_UPD_DT_TM_DA
FROM C0B.T3333 A
WHERE ISO_CTRY_ALPH3_CD = 'NZD'
and A.CARD_ACCT_NO = B.CARD_ACCT_NO
ORDER BY CARD_ACCT_NO,HIST_UPD_DT_TM_DA DESC
fetch first 2 rows only) A2 |
|
|
| Back to top |
|
|
annu zacson
New User
Joined: 19 Aug 2008
Posts: 15
Location: Cochin
|
|
|
|
Hi,
The query worked. Thanks a lot!!
Regards,
Annu |
|
| Back to top |
|
|
|
|
