Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 13 Jul 2005 @ 07:29:52 GMT


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


Subj:   Re: Question regarding Selection limits
 
From:   Lunn, Jonathan

I've been watching this thread and sure there had to be a way. Here's a quick select from DBC.Tables that picks all of the tables from the DBC database and adds however many more are needed to get 200 rows. It relies on the V2R6 concept of TOP N, but there are probably ways to accomplish the same thing in V2R5 using RANK. Try this kind of approach:

     SELECT  TOP 200 *

     FROM (SELECT 1,
     TABLENAME
     , DATABASENAME
     FROM DBC.TABLES
     WHERE TABLEKIND = 'T'
     AND   DATABASENAME = 'DBC'

     UNION

     SELECT 2,
     TABLENAME
     , DATABASENAME
     FROM DBC.TABLES
     WHERE TABLEKIND = 'T'
     AND   (TABLENAME
     , DATABASENAME) NOT IN (SELECT TABLENAME
     , DATABASENAME
     FROM DBC.TABLES
     WHERE TABLEKIND = 'T'
     AND   DATABASENAME = 'DBC')) DT (SEQ_NUM,TABLENAME, DATABASENAME)

     ORDER BY 1;

Hope this helps,

Jonathan



     
  <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: 15 Jun 2023