Home » SQL & PL/SQL » SQL & PL/SQL » ROW_NUMBER() function help
ROW_NUMBER() function help [message #214931] Thu, 18 January 2007 11:57
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I need to capture the earliest occurance for when a proceedure was completed. I'm trying to use the ROW_NUMBER() function
but the problem I'm having is that there can be 'other' results besides 'Shthdc' for the same field: RESULT_VALUE. I want to assign a row_number to the each occurance of 'Shthdc'. Here is the data in the db:


CLASS_NAME	NAME1			NAME2		RESULT_VALUE		LABEL_SEQ	PDATE		RSN1

Cardiovascular	Site Intervention	Sht Site	BldrawLineflShthdcWlido	150154		01/09/07 18:29	1
Cardiovascular	Site Intervention	Sht Site	BldrawShethiWilido	150154		01/09/07 16:00	1
Cardiovascular	Site Intervention	Sht Site	Shthdc			150154		01/09/07 20:01	1



I want the third entry above to have an RSN1 value of 2 but I'm not sure how to go about this. Is there a different function I should be using for this?
Here is my code:


SELECT /*+ ORDERED */
   p.pat_seq,
   pcql.class_name,
   pcql.name1,
   pcql.name2,
   pr.result_value,
   pcql.name1 ||' - '|| pr.result_value RESULT,            
   pr.label_seq,
   DDT.TODATE(pr.perform_ddt) PDATE,
   CASE
      WHEN pr.result_value LIKE ('%Shthdc%')
      THEN ROW_NUMBER() OVER (PARTITION BY p.pat_seq,pr.result_value ORDER BY pr.perform_ddt ASC)
      ELSE ROW_NUMBER() OVER (PARTITION BY p.pat_seq,pcql.class_name,pcql.name1,pr.result_value ORDER BY pr.perform_ddt DESC)
   END RSN1
FROM
   p,
   pr,
   pcql
WHERE
   p.pat_seq = pr.pat_seq  AND
   pr.label_seq = pcql.label_seq  AND
   p.dept_id IN ('AAF') AND
   pr.label_seq IN (150154) AND
   pr.pat_seq IN
      (SELECT 
         p.pat_seq
      FROM
         p,
         pr
      WHERE
         p.pat_seq = pr.pat_seq AND
         (p.discharge_dt > TRUNC(SYSDATE-1) OR p.discharge_dt IS NULL) AND
         p.facility_id = 'F' AND
         p.dept_id IN ('CCF','ICF','IMF','NIF','IMC','3NF','LAB','CTL','TRNG') AND
         (pr.label_seq = (150154) AND pr.result_value LIKE '%Shthdc%') AND
         pr.perform_ddt BETWEEN DDT.FROMDATE(TRUNC(SYSDATE-1)) AND DDT.FROMDATE(TRUNC((sysdate-1)+((23/24)+(59/1440)))))
Previous Topic: Want to copy/append data in one table to another
Next Topic: FOR ALL ?
Goto Forum:
  


Current Time: Sat Dec 10 08:45:06 CST 2016

Total time taken to generate the page: 0.13291 seconds