CODEWITHSUNDEEP
sqloracle-database
tanya nair
tanya nair

Reputation: 53

MAX NOT WORKING IN SQL QUERY

I want the latest record to be retrieved by the following query.... but max is not working in the below query. All the rows are getting retrieved instead of the latest one

 SELECT SV.SEGMENT1 TARGETED_INCENTIVE,
                         SIT.ANALYSIS_CRITERIA_ID,
                         SIT.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER,
                         ST.ID_FLEX_NUM,
                         SIT.DATE_FROM,
                         SIT.DATE_TO,
                         MAX (SIT.PERSON_ANALYSIS_ID)

                    FROM FND_ID_FLEX_STRUCTURES_TL STTL,
                         FND_ID_FLEX_STRUCTURES ST,
                         PER_PERSON_ANALYSES SIT,
                         PER_ANALYSIS_CRITERIA SV
                   WHERE     1 = 1
                         AND (STTL.ID_FLEX_STRUCTURE_NAME) LIKE
                                ('%%Tare%')
                         AND STTL.LANGUAGE = USERENV ('LANG')
                         AND ST.ID_FLEX_CODE = STTL.ID_FLEX_CODE
                         AND ST.ID_FLEX_NUM = STTL.ID_FLEX_NUM
                         AND ST.ID_FLEX_NUM = SIT.ID_FLEX_NUM
                         AND ST.ID_FLEX_NUM = SV.ID_FLEX_NUM
                         AND TO_DATE (SIT.DATE_TO) IS NULL
                         AND SIT.ANALYSIS_CRITERIA_ID = SV.ANALYSIS_CRITERIA_ID
                         AND SIT.PERSON_ID = (SELECT PERSON_ID
                                                FROM abc
                                               WHERE ID = :AIN)
                GROUP BY SV.SEGMENT1,
                         SIT.ANALYSIS_CRITERIA_ID,
                         STTL.ID_FLEX_STRUCTURE_NAME,
                         SIT.OBJECT_VERSION_NUMBER,
                         ST.ID_FLEX_NUM,
                         SIT.DATE_FROM,
                         SIT.DATE_TO;

Can anyone guide ?

Upvotes: 0

Views: 1310

Answers (1)

David Faber
David Faber

Reputation: 12485

I'm afraid that's not what MAX() does. MAX() is an aggregate function (though it can be used as a window [analytic] function), so when you get the MAX() of a particular column grouped by other columns, you will get distinct combinations of values for all those other columns.

I think you might want something like this:

SELECT targeted_incentive, analysis_criteria_id
     , object_version_number, id_flex_num, date_from
     , date_to, person_analysis_id
  FROM (
    SELECT sv.segment1 AS targeted_incentive
         , sit.analysis_criteria_id
         , sit.object_version_number
         , st.id_flex_num
         , sit.date_from
         , sit.date_to
         , sit.person_analysis_id
         , RANK() OVER ( ORDER BY sit.person_analysis_id DESC ) rn
      FROM fnd_id_flex_structures_tl sttl
         , fnd_id_flex_structures st
         , per_person_analyses sit
         , per_analysis_criteria sv
     WHERE sttl.id_flex_structure_name LIKE '%Tare%'
       AND sttl.language = USERENV('LANG')
       AND st.id_flex_code = sttl.id_flex_code
       AND st.id_flex_num = sttl.id_flex_num
       AND st.id_flex_num = sit.id_flex_num
       AND st.id_flex_num = sv.id_flex_num
       AND sit.date_to IS NULL
       AND sit.analysis_criteria_id = sv.analysis_criteria_id
       AND sit.person_id = ( SELECT person_id FROM abc
                              WHERE id = :AIN )
) WHERE rn = 1;

The RANK() window function will return the rank of each row ordered by the value of person_analysis_id in descending order. To get the maximum value, simply filter for rank = 1. Note that this will return more than one row in case of ties. If you want only one row, use ROW_NUMBER() in place of RANK().

Also note that I cleaned up the query a bit. You certainly don't need to use two % wildcards in a row in a LIKE, for example. You also definitely don't need the WHERE 1=1 condition.

Upvotes: 1

Related Questions