Re: Tricky query

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 - 10:49:53 CDT