Is there an equivalent to the FIRST function?

Database

Is there an equivalent to the FIRST function?

SELECT DISTINCT‘ 
ON GOING’ AS RPT_TYPE , CURRENT_DATE – CAST(b.TIMESTMP AS DATE) AS AGING ,
D.NAME AS CATEGORY , B.STATUS_ID AS “TYPE” ,INCIDENT_ID AS INCIDENT_ID,
E.RISK AS RISK_REASON , C.STATUS_NAME AS STAT_DESCRIPTION , I.COMP_UNT_NM AS DIVISION ,
F.AGT_FRST_NM AS FIRST_NAME ,F.AGT_LAST_NM AS LAST_NAME ,(CURRENT_TIMESTAMP (FORMAT 'MM/DD/YYYY HH:MI:SS')(CHAR(20))) AS REPORT_RUN_DATE
FROM
(
SELECT WARNING_ID AS INCIDENT_ID, SRC_WARNING_KEY FROM WARNINGS
UNION
SELECT BUREAU_ID AS INCIDENT_ID, SRC_WARNING_KEY FROM BUREAU
) A INNER JOIN WORK_HISTORY B
ON A.SRC_WARNING_KEY = B.ARTFCT_KEY
INNER JOIN WORK_JOB_STATUS C
ON B.WORK_JOB_STATUS ID = C.WORK_JOB_STATUS_ID
INNER JOIN ORGANIZATION_HIER D
ON D.ORG_ID = B.ORG_ID
LEFT OUTER JOIN WARNINGS_DTL_MEMO E
ON E.SRC_WARNING_KEY = A.SRC_WARNING_KEY
LEFT OUTER JOIN AGT_PRFL F
ON F.AGTID = CAST (B.CREAT_BY_AGTID AS DECIMAL(38,0))
LEFT OUTER JOIN AGNT_GROUP_RELATION G
ON F.AGTID = CAST(G.AGTID AS DECIMAL(38,0))
LEFT OUTER JOIN GROUP_ORGANIZATION_HIER H
ON G.GRP_ID = H.GRP_ID
LEFT OUTER JOIN ORGANIZATION_DIVISION I
ON I.SRC_ORG_UNT = H.SRC_ORG_UNT
WHERE WORK_JOB_STATUS ID IN ('5609','6934','5128','5118')

Hi,

I am trying to return unique rows for bureaus and warnings from an incident database and have run into a related table that has a one to many relationship. The situation is this a warning or bureau could have many reasons why it was logged but all I require is one (the first reason why the incident was logged).

Without a first function (sorry I use Access a lot), how do I resolve the join the Warnings Dtl Memo table that many reasons that are returned because of the left join?  There are 10's of thousands of records to consider and a Select Distinct will not work.  How is an issue such as this resolved using SQL?  In the code the column is E.Risk as Risk Reason.

Let me know your thoughts

Thanks.

1 REPLY

Re: Is there an equivalent to the FIRST function?

Hi,

I am not sure about the exact date column here. From what you have told, it seems that you need the distinct number of Incident Id and if there are duplicates on it, we should take the incident_id about when it was created.

We have QUALIFY Functions which will act on ROW_NUMBER() and RANK() Window functions that will get the required one.

SELECT 'ON GOING' AS RPT_TYPE , CURRENT_DATE – CAST(b.TIMESTMP AS DATE) AS AGING ,
    D.NAME AS CATEGORY , B.STATUS_ID AS “TYPE” ,INCIDENT_ID  AS INCIDENT_ID,
    E.RISK AS RISK_REASON , C.STATUS_NAME AS STAT_DESCRIPTION ,
  I.COMP_UNT_NM AS DIVISION ,   F.AGT_FRST_NM AS FIRST_NAME ,F.AGT_LAST_NM AS LAST_NAME ,
  (CURRENT_TIMESTAMP (FORMAT 'MM/DD/YYYY HH:MI:SS')(CHAR(20))) AS REPORT_RUN_DATE   
FROM  ( 
SELECT WARNING_ID AS  INCIDENT_ID, SRC_WARNING_KEY  
FROM WARNINGS 
UNION
 SELECT BUREAU_ID AS  INCIDENT_ID, SRC_WARNING_KEY
FROM BUREAU   ) A INNER JOIN   WORK_HISTORY B  
 ON   A.SRC_WARNING_KEY = B.ARTFCT_KEY   INNER JOIN   WORK_JOB_STATUS C  
 ON   B.WORK_JOB_STATUS ID = C.WORK_JOB_STATUS_ID   INNER JOIN   ORGANIZATION_HIER D  
 ON   D.ORG_ID = B.ORG_ID   LEFT OUTER JOIN   WARNINGS_DTL_MEMO E  
 ON   E.SRC_WARNING_KEY = A.SRC_WARNING_KEY   LEFT OUTER JOIN   AGT_PRFL F  
 ON   F.AGTID = CAST (B.CREAT_BY_AGTID AS DECIMAL(38,0))   LEFT OUTER JOIN   AGNT_GROUP_RELATION G  
 ON   F.AGTID  = CAST(G.AGTID AS DECIMAL(38,0))     LEFT OUTER JOIN   GROUP_ORGANIZATION_HIER H  
 ON   G.GRP_ID = H.GRP_ID   LEFT OUTER JOIN   ORGANIZATION_DIVISION I  
 ON   I.SRC_ORG_UNT = H.SRC_ORG_UNT   
WHERE   WORK_JOB_STATUS ID IN ('5609','6934','5128','5118')
QUALIFY ROW_NUMBER() OVER( PARTITION BY INCIDENT_ID order by REPORT_RUN_DATE ASC   )=1

I have given the REPORT_RUN_DATE in the Order by clause, I am not sure abt the date coulmn that you have. This will give the distinct Incident_Ids.

Thanks & Regards,

Adharssh Rao.