Limiting records

[unifex]

Well i should probably be embarrassed about asking this because i'm sure it's really simple - just haven't found anything covering it already.

Is it possible to limit the number or records returned by an SQL query? All i want to do is run a test job and check the output but don't want to have to wait 10 mins every time.

There's obviouslt the SAMPLE function but that still seems to take just as long.

Any ideas.

Cheers

Unifex

 

[dnoeth]

In a complex query the Sampling is done _after_ the result spool file has been created, that's why it's not much faster.

You could use a sample on a large table in a derived table like:
select something_really_complex
from (select * from mytable sample 1000) mytab join to_lots_of_other_tables

But the best way is probably to run the test on a small subset of the data in your development environment.

Dieter

 

[GSherlin]

When we have a numeric key, we use:
WHERE TMP.DW_CLM_KEY MOD 100 = 5
It returns about 1% of the table.
As you increase the 100 to larger numbers, you get fewer records returned in your results set. For more records, make it smaller.

 

[saamy]

Dieter,

By using 'sample' in select statement,can we always get the next set of records?
eg:- I have got 10 records in the table 'customer'
select * from customer sample 5;
When I executed this statement twice,I found that it doesnt give the next 5 records.There were some common records.

What is the basis on which 'sample' selects records?

Saamy

 

[unifex]

I'm not certain what you mean by the 'next set of records'. The SAMPLE statement is random so you may well get common records between the two. You may also find that it doesn't always give exactly the number you request either, i'm guessing that this is due to it using a percentage of the total to return.

So basically your statement will select close to 5 random records from the total.

Oh and check out the 2nd message off the top with regards the speed of it too.

Unifex

 

[saamy]

Thanks.I just wanted to make sure that its selected randomly.
Is there some method by which we can limit the number of records retrived from a select statemnt to a fixed number and if we try to execute the same select again then the remaining number of records are retrieved?
eg:- I have got a table with 20 records
Using a selct statement can I retrive 5 distinct records each?

Saamy

 

[unifex]

Well now you're asking!

I'm not certain exactly how to do this but I have a thought you might try.

If you use a Rank of some sort to number the records and then just select until >5. Then you'd need to delete them from the table so that next time you'd get the next 5.

Not a pretty way of doing it but it might work.

Dunno if it could be done with Macro or Procedure any better (or just 'normally'!!!!!)

 

[dnoeth]

"Using a selct statement can I retrive 5 distinct records each?"

Use several samples in one query and materialize the result:
create table sampledata as
(select col1,col2,...., SAMPLEID as sid
from tab
SAMPLE 5,5,5,5
) with data;

This will create 4 distinct samples with 5 rows each.
If you want 25% of the rows:
SAMPLE 0.25,0.25,0.25,0.25

"Is there some method by which we can limit the number of records retrived from a select statemnt to a fixed number and if we try to execute the same select again then the remaining number of records are retrieved?"

A sample is random and not repeatable, because there's no randomseed.
Materialize the sample, so you can use a NOT IN/NOT EXISTS
for the next query.

Dieter