CODEWITHSUNDEEP
sqlnetezzaaginity
gstatus
gstatus

Reputation: 15

Return Record With Earliest Date

I am using Aginity Workbench with a database on Netezza and I am trying to return the record with the earliest date based on it featuring an IS code in any one of three (serviceability) columns. There are multiple records for one ICS_UID, but I just want to return the record where the earliest occurrence of it having an IS code.

Below is the code I have been trying to use but it seems to be returning all instances of where a record has an IS code and not the selection of ICS_UID's in the where clause. Grateful for any help or advice. 

SELECT 
ICS _UID, min(MOVEMENT_DATE) as MOVEMENT_DATE, CURRENT_A_SERVICABILITY_CODE, CURRENT_B_SERVICABILITY_CODE, 
CURRENT_C_SERVICABILITY_CODE 
FROM 
HUB_MOVEMENT
WHERE 
ICS_UID IN (317517607,317962513,etc,etc…)
AND CURRENT_A_SERVICABILITY_CODE = 'IS' OR CURRENT_B_SERVICABILITY_CODE = 'IS' OR CURRENT_C_SERVICABILITY_CODE = 'IS'
GROUP BY 
ICS_UID, CURRENT_A_SERVICABILITY_CODE,
CURRENT_B_SERVICABILITY_CODE,
CURRENT_C_SERVICABILITY_CODE;

Upvotes: 1

Views: 573

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Don't use GROUP BY. If you want one record, then:

SELECT m.*
FROM HUB_MOVEMENT m
WHERE ICS_UID IN (317517607,317962513,etc,etc…) AND
      'IS' IN (CURRENT_A_SERVICABILITY_CODE, CURRENT_B_SERVICABILITY_CODE , CURRENT_C_SERVICABILITY_CODE)
ORDER BY MOVEMENT_DATE
LIMIT 1;

If you want one row per ICS_UID, then you can use ROW_NUMBER():

SELECT m.*
FROM (SELECT m.*,
             ROW_NUMBER() OVER (PARTITION BY ICS_UID ORDER BY MOVEMENT_DATE) as seqnum
      FROM HUB_MOVEMENT m
      WHERE ICS_UID IN (317517607,317962513,etc,etc…) AND
            'IS' IN (CURRENT_A_SERVICABILITY_CODE, CURRENT_B_SERVICABILITY_CODE , CURRENT_C_SERVICABILITY_CODE)
     ) m
WHERE seqnum = 1;

Upvotes: 1

Related Questions