Selecting Some Rows

[amerbaig]

A table comprises 30 million rows. I want to retrive only first 100 rows of it. This will help me to just see the values in table. Can I do so without where clause?

Amer

 

[SiMCard]

Use the SAMPLE command - it has 2 forms:

Using an INTEGER for the SAMPLE value will return that number of rows, so the following returns 100 rows:

SEL * FROM <table> SAMPLE 100;

Using a decimal fraction for the SAMPLE value will return that fraction of the total number of rows, so the following returns 32 rows from a 100 row table:

SEL * FROM <table> SAMPLE 0.32;

You can do multiple SAMPLES, seperating your SAMPLE numbers with a comma (e.g. SAMPLE 100, 200).

Bear in mind (from your question) that this does not return the 'first' rows from the table, as the set theory used by Teradata has no concept of 'first', unless an order clause is used. To get the 'first' 100 rows, you would need to do something like the following, assuming that <table> has a UPI on PART_NUMBER, and you want the lowest 100 PART_NUMBERs:

SELECT *
FROM
 <table> T1
   INNER JOIN
 (SELECT PART_NUMBER
  FROM ( SELECT
          PART_NUMBER
         ,RANK ( PART_NUMBER ASC ) as PN_Rank
         FROM <table>
        ) NT1
  WHERE NT1.PN_RANK <= 100
 ) DT1
     ON  DT1.PART_NUMBER = T1.PART_NUMBER
ORDER BY PART_NUMBER
;

Hope this helps...

Si M...

 

[amerbaig]

Yes -- It did

Thanks for great help

Amer