Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 09 Oct 2000 @ 16:23:19 GMT


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


Subj:   Re: V2R3 SQL Documentation - Rank Function
 
From:   David Hough

The RANK function sorts the input rows by the columns in the sort expression list, then assigns a rank value from 1-n based on the position in the sort (identical sort values get identical ranks). The rows are still returned in whatever order the system happens to generate them, which is why you need the ORDER BY if you want them is some particular order.

By default, RANK collects all the rows from a table into a single group before ranking them in descending order. If you want to see the most recent 5 logons from dbc.logonoff, you can do this:

     select username, logondate, logontime
     from dbc.logonoff
     where event = 'Logon'
     qualify rank(logondate, logontime) <= 5
     order by username, logondate, logontime;

If you want the least recent 5 logons, you can override the rank ordering with ASC:

     select username, logondate, logontime
     from dbc.logonoff
     where event = 'Logon'
     qualify rank(logondate asc, logontime asc) <= 5
     order by username, logondate, logontime;

You can also specify RANK groups other than the whole table via the GROUP BY clause (aggregate functions and qualify don't mix, though). Typically, you GROUP BY one set of columns and RANK by another. If you want the most recent 5 logons from dbc.logonoff for each userid, you can do this:

     select username, logondate, logontime
     from dbc.logonoff
     where event = 'Logon'
     group by username
     qualify rank(logondate, logontime) <= 5
     order by username, logondate, logontime;

If you need to use aggregation and qualify in the same query, you have to use derived tables.

Hope this helps!



     
  <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