Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 30 Apr 2008 @ 17:51:02 GMT


     
  <Prev Next>   <<First <Prev Next> Last>>  


Subj:   Re: Retrieving the Second Highest Amount
 
From:   de Wet, Johannes M

Megan - How about something like the following? This should take care of not needing a QUALIFY statement and being able to select specific dates. I used the Ranking Descending and Ascending in the query below to help detect Customers with only one row, i.e. no 2nd place.

I wasn't sure though if you had a tool restriction with the WHERE as well - if you do, this may not work for you.

     SELECT
       CUSTOMER
      ,CUST_DATE
      ,CUST_AMT
      ,RANKVAL_DESC
      ,RANKVAL_ASC
     FROM
        (SELECT
        CUSTOMER
       ,CUST_DATE
       ,CUST_AMT
       ,ROW_NUMBER() OVER (PARTITION BY CUSTOMER
                                  ORDER BY CUST_AMT DESC) AS RANKVAL_DESC
       ,ROW_NUMBER() OVER (PARTITION BY CUSTOMER
                                  ORDER BY CUST_AMT ASC ) AS RANKVAL_ASC
    FROM
       MYCUST_TABLE A
    WHERE
       CUST_DATE GE '2008-03-01'
    ) A
    WHERE
       RANKVAL_DESC = 2
     OR
       (RANKVAL_ASC = 1 AND RANKVAL_DESC = 1);

Johannes de Wet
Unum



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016