Archives of the TeradataForum

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

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

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>>