Multiple Min Dates

[Anonymous]

DATA:

ACCOUNT_ID	LOG_DATE	TYPE	SUCCESS	CREDIT_OR_DEBIT	AMOUNT
1	2-Jan	A	C	C	1
2	3-Jan	B	C	C	5
3	4-Jan	B	C	C	45
4	5-Jan	B	C	C	43
5	6-Jan	C	C	624
6	7-Jan	C	C	C	453
7	8-Jan	A	C	C	7
8	9-Jan	A	C	C	48676
9	10-Jan	A	C	C	486
10	11-Jan	A	C	C	5
11	5-Jan	C	C	4864
1	6-Jan	B	C	C	4
2	7-Jan	C	C	C	4
3	8-Jan	C	C	C	45
4	9-Jan	C	C	C	486
5	17-Jan	C	C	C	46
6	18-Jan	C	C	467
7	19-Jan	A	C	C	64
8	5-Jan	A	C	C	5
9	6-Jan	C	C	245
10	7-Jan	C	C	6
11	8-Jan	B	C	C	45
23	9-Jan	B	C	C	465
5	25-Jan	C	C	1

Can someone explain why this doesnt work? Please

     MINDATES:

        LOAD

            ACT,

            FLOOR(MIN(DATE)) as FIRST_DATE,

            IF(MATCH(UPPER(TYPE), 'A'), FLOOR(MIN(DATE))) as A_MIN_DATE,

            IF(MATCH(UPPER(TYPE), 'B', 'C'),  FLOOR(MIN(DATE))) as BC_MIN_DATE

        FROM AttachedFile

        WHERE MATCH(UPPER(SUCCESS), 'C','Y')

        GROUP BY ACT;

I want to get min date for each scenario. Please help!

[swuehl]

You need to use an aggregation function on all fields in your LOAD not listed in the group by.

    MINDATES:

        LOAD

            ACT,

            FLOOR(MIN(DATE)) as FIRST_DATE,

           FLOOR(MIN( IF(MATCH(UPPER(TYPE), 'A'), DATE))) as A_MIN_DATE,

            FLOOR(MIN( IF(MATCH(UPPER(TYPE), 'B', 'C'),DATE))) as BC_MIN_DATE

        FROM AttachedFile

        WHERE MATCH(UPPER(SUCCESS), 'C','Y')

        GROUP BY ACT;