CODEWITHSUNDEEP
databaseteradata
Suman Verma
Suman Verma

Reputation: 55

TeraData Query to get min and max from a table

I have a table named HMS_PGHL8_HQID_MAP_DEMO and database named UDMTOOL_STG.

Below query fetches me reuired values.

SELECT udm_main.RECORD_ID AS  record_id ,udm_main.HMS_PGH_NODE_ID AS  hms_pgh_node_id, 
       udm_main.HMS_PGH_NODE_NAME AS hms_pgh_node_name ,udm_main.HQ_NODE_ID AS hq_node_id, udm_main.HQ_NODE_NAME AS hq_node_name,
       udm_main.NODE_TYPE_IND as node_type_ind,UPDATED_ON,UPDATED_BY,
       INSERT_TS  FROM UDMTOOL_STG.HMS_PGHL8_HQID_MAP_DEMO udm_main
WHERE op_code <> 'D' OR op_code IS NULL
ORDER BY RECORD_ID,HMS_PGH_NODE_ID,HQ_NODE_ID
GROUP BY 1,2,3,4,5,6,7,8,9;

Now i need to get the total row values whose:

  1. record ID is min
  2. record ID as max

Please note that below do not works,

SELECT MIN(udm_main.RECORD_ID AS)  record_id ,udm_main.HMS_PGH_NODE_ID AS  hms_pgh_node_id, 
           udm_main.HMS_PGH_NODE_NAME AS hms_pgh_node_name ,udm_main.HQ_NODE_ID AS hq_node_id, udm_main.HQ_NODE_NAME AS hq_node_name,
           udm_main.NODE_TYPE_IND as node_type_ind,UPDATED_ON,UPDATED_BY,
           INSERT_TS  FROM UDMTOOL_STG.HMS_PGHL8_HQID_MAP_DEMO udm_main
    WHERE op_code <> 'D' OR op_code IS NULL
    ORDER BY RECORD_ID,HMS_PGH_NODE_ID,HQ_NODE_ID
    GROUP BY 1,2,3,4,5,6,7,8,9;

Requesting your help to resolve the issue.

Upvotes: 1

Views: 1176

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You can use QUALIFY to filter the MIN/MAX:

SELECT udm_main.RECORD_ID,udm_main.HMS_PGH_NODE_ID AS  hms_pgh_node_id, 
       udm_main.HMS_PGH_NODE_NAME AS hms_pgh_node_name,udm_main.HQ_NODE_ID AS hq_node_id, udm_main.HQ_NODE_NAME AS hq_node_name,
       udm_main.NODE_TYPE_IND as node_type_ind,UPDATED_ON,UPDATED_BY,
       INSERT_TS
FROM UDMTOOL_STG.HMS_PGHL8_HQID_MAP_DEMO udm_main
WHERE op_code <> 'D' OR op_code IS NULL
GROUP BY 1,2,3,4,5,6,7,8,9
QUALIFY udm_main.RECORD_ID = MIN(udm_main.RECORD_ID) OVER ()
     OR udm_main.RECORD_ID = MAX(udm_main.RECORD_ID) OVER ()
ORDER BY RECORD_ID

;

Upvotes: 3

Related Questions