How to get only minimum date account only

[mahnoor1279]

i'm struggling with this problem but failed to find the correct answer , that how to get only the account with minimum date not showing the multiple account  for this i have two tables 

complete_account:
LOAD
CUST_SNO,
CUST_ACCT_CODE,
 CONTACT_NO,
CUST_ACCT_SNO,
ENTER_DATE,
ENTER_BY,
EMAIL_INACTIVE,
UPDATE_FROM,
CRM_STATUS
FROM .....
(qvd) ;

Concatenate

LOAD
ONLINE_ACCT_CAT_INVEST_LIMIT,
PK_CUST_ACCT_SNO, 
FIRST_NAME,
MARITAL_STATUS,
CUST_SNO AS PK_CUST_SNO,
SALES_AGENT,
CUST_ACCT_CODE ,
ACCT_OPEN_DATE,
ROW_ID
FROM .....(qvd);

ALL_DATA:
LOAD
FIRST_NAME,min([ACCT_OPEN_DATE]) as MIN_ACCT_OPEN_DATE

Resident complete_account group by FIRST_NAME;

[rubenmarin]

Hi to keep the only the rows with min([ACCT_OPEN_DATE]) you can use an inner Join:

Table:
LOAD [fields]
from [qvd]

Inner Join (Table)
LOAD PK_CUST_ACCT_SNO, // Or the field(s) used as primary key
  min([ACCT_OPEN_DATE]) as ACCT_OPEN_DATE // same field name to reduce data
resident Table;

[mahnoor1279]

Do i need to add group by  

like this

inner join (CUSTOMER_ACCOUNT_TABLE)

Load
FIRST_NAME,
PK_CUST_SNO,
min([ACCT_OPEN_DATE]) as ACCT_OPEN_DATE

Resident CUSTOMER_ACCOUNT_TABLE group by FIRST_NAME,PK_CUST_SNO;

[rubenmarin]

Yes, I forgot to add it but you need to add in a group all fields not enclosed in an aggreation function.