Re: Tricky query

On Wed, 30 May 2001 23:49:53 +0800, Dino Hsu <dino1_at_ms1.hinet.net> wrote:

Oracle 8i has a ranking function as I was shown this morning. Prior to Oracle 8 I would think some complex PROC or PL/SQL was required.

>On Tue, 29 May 2001 20:03:55 GMT, techsup_at_mindspring.com (James
>Williams) wrote:
>
>>I got a query I am trying to compose. Its complex to me!
>>
>>I need to create a query where three oldest records with like
>>ky_premise_no is sorted by order_completion date
>>
>>
>>The DTE and TME (Don't ask why the developers didn't use DATE)
>>will be used to determine the age. The ky_premise_no can be duplicates
>>per unique source_order_nbr. The query would only pull back the three
>>oldest duplicate ky_prem_no and order by order_completion_date.
>>
>> ----------------------------------------- --------
>>----------------------------
>> SOURCE_ORDER_NBR NOT NULL VARCHAR2(30)
>> KY_PREM_NO NOT NULL NUMBER(38)
>> DTE VARCHAR2(8)
>> TIME VARCHAR2(6)
>> DESTINATION_APP_ID VARCHAR2(4)
>> RECORD_TYPE VARCHAR2(1)
>> RECORD_SUB_TYPE VARCHAR2(1)
>> RECORD_FUNCTION VARCHAR2(1)
>> RECORD_SEQUENCE_NBR VARCHAR2(2)
>> WORK_GROUP_ID VARCHAR2(9)
>> WORK_ORDER_NBR VARCHAR2(6)
>> ORDER_COMPLETION_DTE VARCHAR2(8)
>> ORDER_TYPE VARCHAR2(6)
>> ACTION_TAKEN VARCHAR2(12)
>> EMPLOYEE_ID VARCHAR2(8)
>> COMMENTS VARCHAR2(255)
>> LAST_PAY_AMOUNT NUMBER(12,2)
>> LAST_PAY_DTE VARCHAR2(8)
>> BALANCE_DUE NUMBER(11,2)
>> DUE_DTE VARCHAR2(8)
>> DELINQUENT_AMOUNT NUMBER(11,2)
>> NBR_OF_CUTOFFS NUMBER(5)
>> CREDIT_INFO VARCHAR2(12)
>
>I try to re-phrase the questions as:
>"An order, identified by SOURCE_ORDER_NBR, can contain one or more
>premises, identified by KY_PREM_NO, thus one or more records in this
>table, say TB_SALES_DETAIL. The question is to get the three oldest
>(determinned by DTE+TIME) premises, if any, for each order, and the
>resulting list of premises is, in turn, sorted by order completion
>date."
>
>(Assume the date format is 'yyyymmdd', and the time format is
>'hhmmss')
>
>At the moment, I cannot figuer out a SQL to do it. The key lies in how
>to get the 'ranking in group' for each premise within its
>corresponding order, if this could be done, it'd be easy to solve the
>question with a double-query SQL. I wish someone could solve this
>soon.
>
>Dino
>
Received on Wed May 30 2001 - 20:35:57 CDT