8 Replies Latest reply: Aug 28, 2015 7:33 AM by Jose Luis Dengra RSS

    group by must have all fields in the select, why?

      hi everyone,

       

      i have

      SR_NOSR_STATUSSR_STATUS_DATE
      123Open01 jan
      123Closed15 jan

       

      i want to know what's the latest status for each SR, one SR being identified by its number (SR_NO)

       

      i'm trying to load a temporary table as :

      SR_TMP:

      load SR_NO,

           SR_STATUS,

           SR_STATUS_DATE,

           max(SR_STATUS_DATE) as MAX_SR_STATUS_DATE

      from [$(vQVDBasePath)\SERVICE_REQUESTS_STATUSES.qvd] (qvd)

      group by SR_NO;

       

      so that in a subsequent step i can do:

      load SR_NO, SR_STATUS

      resident SR_TMP

      where SR_STATUS_DATE = MAX_SR_STATUS_DATE;

       

      but this errors and i found that that's because all the fields in the select clause must be in the group by clause, but if i do this, it won't give me the most recent status for each SR.

       

      how can i do this?

       

      thanks for your help

      Alex