Home » SQL & PL/SQL » SQL & PL/SQL » DENSE RANK / FIRST_VALUE help
DENSE RANK / FIRST_VALUE help [message #207225] Mon, 04 December 2006 13:50 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm trying to pull the first and last charting dates for the RESULT_VALUE field using the DENSE RANK or FIRST_VALUE/LAST_VALUE functions with no success. Can somebody please tell me where I'm going wrong.

Here's my code:

SELECT DISTINCT
   p.name
   p.pid,
   pr.PS,
   FIRST_VALUE(DDT.TOCHAR(pr.perform,'MM/DD/YYYY HH24:MI')) OVER ( ORDER BY pr.PS) First_Chart,
   pr.result_value
FROM
   pat_results pr,
   pcq_label pcql,
   patient p
WHERE
       pr.PS = p.PS
   AND pcql.label = pr.label  
   AND pcql.name IN ('Risk')





(I've also tried this line instead of FIRST_VALUE
   MIN(pr.result_value) KEEP (DENSE_RANK LAST ORDER BY pr.perform) OVER (PARTITION BY p.PS) AS First_Charted)









I'm getting the following output with the date being correct but listed for each RESULT_VALUE:


Name		PID	DEPT	FIRST_CHART	RESULT_VALUE
TEST,TEST	1234	B	11/27/2006	19
TEST,TEST	1234	B	11/27/2006	20
TEST,TEST	1234	B	11/27/2006	21
TEST,TEST	1234	B	11/27/2006	22
TEST,TEST	1234	B	11/27/2006	23


But I would like the result set to be like so:
Name		PID	DEPT	LAST_CHART	RESULT_VALUE
TEST,TEST	1234	B	11/27/2006	19
TEST,TEST	1234	B	12/01/2006	23 (for LAST_VALUE)



Thanks,
Stan
Re: DENSE RANK / FIRST_VALUE help [message #207254 is a reply to message #207225] Mon, 04 December 2006 19:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If I understand right, you want the minimum and maximum dates for each combination of Name, PID, and DEPT, and then you want the result_value that corresponds to those min and max rows.

If you are happy for it to come out on one row instead of two...
SELECT 
   p.name
   p.pid,
   pr.PS,
   DDT.TOCHAR(MIN(perform),'MM/DD/YYYY HH24:MI') First_Chart,
   MIN(pr.result_value) KEEP (
       DENSE_RANK FIRST ORDER BY pr.perform) First_Result,
   DDT.TOCHAR(MAX(perform),'MM/DD/YYYY HH24:MI') Last_Chart,
   MIN(pr.result_value) KEEP (
       DENSE_RANK LAST ORDER BY pr.perform) Last_Result
FROM
   pat_results pr,
   pcq_label pcql,
   patient p
WHERE
       pr.PS = p.PS
   AND pcql.label = pr.label  
   AND pcql.name IN ('Risk')
GROUP BY 
   p.name
   p.pid,
   pr.PS


You could convert it to two rows by joining to a row generator (search the board). It is possible to do it with analytic functions, but I don't like them because they are not very efficient, and they just seem wrong when you you really want to do is aggregate.

Ross Leishman
Re: DENSE RANK / FIRST_VALUE help [message #207280 is a reply to message #207254] Mon, 04 December 2006 23:05 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thanks. That did it. I initially made a typo.

Thanks,
Stan

[Updated on: Tue, 05 December 2006 07:31]

Report message to a moderator

Previous Topic: Oracle Package.Function call from VB.Net?
Next Topic: import and export problem
Goto Forum:
  


Current Time: Sat Dec 03 14:00:13 CST 2016

Total time taken to generate the page: 0.10328 seconds