CODEWITHSUNDEEP
sqloracle-database
Madhusudan
Madhusudan

Reputation: 4815

How to retrieve first value for a column in Oracle database?

I have written a query and here below I mentioned.

    SELECT P.PERSON_ID,
                P.BIRTH_DATE,
                P.GENDER_CODE,
                LISTAGG(R.RACE_CODE, ',') WITHIN GROUP(ORDER BY R.RACE_CODE) AS RACE_CODE,
                P.HISPANIC_LATINO_CODE,
                C.CLINICALLY_DIAGNOSED_CODE,
                LISTAGG(C.CHARACTERISTIC_CODE, ',') WITHIN GROUP(ORDER BY C.CHARACTERISTIC_CODE) AS CHARA_CODES,
                P.PREV_ADOPTED_CODE,
                P.AGE_ADOPTED,
                PE.REMOVED_DATE
  FROM PERSON P
  LEFT OUTER JOIN RACE R
    ON P.PERSON_ID = R.PERSON_ID
  LEFT OUTER JOIN CHARACTERISTIC C
    ON C.PERSON_ID = P.PERSON_ID
  LEFT OUTER JOIN PLACEMENT_EPISODE PE
    ON P.PERSON_ID = PE.CHILD_ID
 GROUP BY P.PERSON_ID,
          P.BIRTH_DATE,
          P.GENDER_CODE,
          P.HISPANIC_LATINO_CODE,
          C.CLINICALLY_DIAGNOSED_CODE,
          P.PREV_ADOPTED_CODE,
          P.AGE_ADOPTED,
          PE.REMOVED_DATE

Here, I want to change my query so that it will retrieve only first value for pe.removed_date.

I read about FIRST() function here. But I am not able to change my query to use FIRST() function.

Suppose PERSON table holds person_id's 1,2,3... And PLACEMENT_EPISODE holds values like:

person_id   removed_date
   1          d1_1
   2          d2_1
   1          d1_2
   3          d3_1
   2          d2_2
   1          d1_3

So i want:

 person_id           removed_date
  1            d1_1
  2            d2_1
  3            d3_1

child_id from PLACEMENT_EPISODE table is foreign key references Primary key person_id from PERSON table.

Upvotes: 1

Views: 2072

Answers (2)

T.Y. Kucuk
T.Y. Kucuk

Reputation: 477

You can use ROW_NUMBER() OVER (PARTITION BY.. to get order number by specified clause. Wrap your statement with select * from Then use that order number in its where clause like in this SQL:

select * from ( 
 SELECT         P.PERSON_ID,
                P.BIRTH_DATE,
                P.GENDER_CODE,
                LISTAGG(R.RACE_CODE, ',') WITHIN GROUP(ORDER BY R.RACE_CODE) AS RACE_CODE,
                P.HISPANIC_LATINO_CODE,
                C.CLINICALLY_DIAGNOSED_CODE,
                LISTAGG(C.CHARACTERISTIC_CODE, ',') WITHIN GROUP(ORDER BY C.CHARACTERISTIC_CODE) AS CHARA_CODES,
                P.PREV_ADOPTED_CODE,
                P.AGE_ADOPTED,
                PE.REMOVED_DATE,
                ROW_NUMBER() OVER (PARTITION BY P.PERSON_ID ORDER BY PE.REMOVED_DATE ASC) AS order_num
  FROM PERSON P
  LEFT OUTER JOIN RACE R
    ON P.PERSON_ID = R.PERSON_ID
  LEFT OUTER JOIN CHARACTERISTIC C
    ON C.PERSON_ID = P.PERSON_ID
  LEFT OUTER JOIN PLACEMENT_EPISODE PE
    ON P.PERSON_ID = PE.CHILD_ID
 GROUP BY P.PERSON_ID,
          P.BIRTH_DATE,
          P.GENDER_CODE,
          P.HISPANIC_LATINO_CODE,
          C.CLINICALLY_DIAGNOSED_CODE,
          P.PREV_ADOPTED_CODE,
          P.AGE_ADOPTED,
          PE.REMOVED_DATE
) WHERE order_num=1

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

If you want just a first selected row you can use rownum.

SELECT DISTINCT P.PERSON_ID,
                P.BIRTH_DATE,
                P.GENDER_CODE,
                LISTAGG(R.RACE_CODE, ',') WITHIN GROUP(ORDER BY R.RACE_CODE) AS RACE_CODE,
                P.HISPANIC_LATINO_CODE,
                C.CLINICALLY_DIAGNOSED_CODE,
                LISTAGG(C.CHARACTERISTIC_CODE, ',') WITHIN GROUP(ORDER BY C.CHARACTERISTIC_CODE) AS CHARA_CODES,
                P.PREV_ADOPTED_CODE,
                P.AGE_ADOPTED,
                PE.REMOVED_DATE
  FROM PERSON P
  LEFT OUTER JOIN RACE R
    ON P.PERSON_ID = R.PERSON_ID
  LEFT OUTER JOIN CHARACTERISTIC C
    ON C.PERSON_ID = P.PERSON_ID
  LEFT OUTER JOIN PLACEMENT_EPISODE PE
    ON P.PERSON_ID = PE.CHILD_ID
 WHERE ROWNUM = 1;
 GROUP BY P.PERSON_ID,
          P.BIRTH_DATE,
          P.GENDER_CODE,
          P.HISPANIC_LATINO_CODE,
          C.CLINICALLY_DIAGNOSED_CODE,
          P.PREV_ADOPTED_CODE,
          P.AGE_ADOPTED,
          PE.REMOVED_DATE

Upvotes: 0

Related Questions