| Resolving dense_rank ties [message #8788] |
Thu, 25 September 2003 12:47  |
Dan Wood
Messages: 11
Registered: January 2003
|
Junior Member |
|
|
With help from member Todd Barry, I was able to construct a query that gives me the ranking by maxox for a set observations within a week's time frame. However, both rank() and dense_rank() create ties. I am trying to resolve the ties by choosing the record with the most recent observation date. And I can't see how to do it. max(peaktime) is the kind of key I'm looking to use, but when placed in the outer select, I have to group by the other fields, with undesirable results, and when placed in the rank of dense_rank select, doesn't seem to affect the output. Any help would be greatly appreciated!
Sample raw data:
CLL LSSN PERIOD MAXOX PEAK
----- -------- --------- ---------- ---------
DC99W TP116823 20-SEP-03 5.0 11:00
DC99W TP116823 21-SEP-03 5.0 11:30
DC99W TP116823 22-SEP-03 4.5 12:00
DC99W TP116823 23-SEP-03 5.0 12:30
DC99W TP116824 20-SEP-03 5.1 13:30
DC99W TP116824 21-SEP-03 4.7 13:30
DC99W TP116824 22-SEP-03 5.1 13:00
DC99W TP116824 23-SEP-03 3.0 13:00
Ranking for TP116823 gives a tie for maxox between:
DC99W TP116823 20-SEP-03 5.0 11:00
DC99W TP116823 21-SEP-03 5.0 11:30
DC99W TP116823 23-SEP-03 5.0 12:30
and for TP116824 between:
DC99W TP116824 20-SEP-03 5.1 13:30
DC99W TP116824 22-SEP-03 5.1 13:00
I wish to select the most recent dated entry from the ties.
DC99W TP116823 23-SEP-03 5.0 12:30
DC99W TP116824 22-SEP-03 5.1 13:00
Any ideas as to how I might incorporate the criteria within the rank select or in the outer select?
Thanks!
|
|
|
|
| Re: Resolving dense_rank ties [message #8789 is a reply to message #8788] |
Thu, 25 September 2003 13:18  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
It sounds like you just need to add a column to the ORDER BY list in the DENSE_RANK to break the tie.
I'm not really sure what your datatypes are for the PERIOD and PEAK columns, but if they are strings (hopefully they are dates instead):
partition by ... order by maxox desc, to_date(period || peak, 'dd-MON-rrhh24:mi') desc
If they are dates, the answer is a bit different but the same concept applies.
|
|
|
|
| Re: Resolving dense_rank ties [message #8796 is a reply to message #8789] |
Thu, 25 September 2003 14:31 |
Dan Wood
Messages: 11
Registered: January 2003
|
Junior Member |
|
|
|
That indeed did the trick, Todd, and taught me more about the logic behind the ranking functions. My next task will be to select from this result set just the top 10 observations within each subgroup. (I didn't show that earlier in order to simplify my need.) I'm truly hoping to be able to complete the remaining logic without help, but I am grateful that there is a real, live human out there who is willing to help out if I should get stuck. Thanks!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
