At 04:06 PM 12-04-02 +0300, Ibrahim Bulut wrote:

>Hi all,
>
>I have got a new idea.
>I want to share my idea with mail group.
>
>Sometimes,
>A lot of records fetch from query.
>But we want first 10 record.
>
>I didn't do it.
>I asked this problem to mail group, my friends.
>But I couldn't do it.
>
>Some of myfriends (They are using Ms Sql Server) told me try following sql
>statement
>SELECT TOP 5 * FROM TABLE
>This sql statement fetchs only first 5 record from table1.
>But I couldn't do it with interbase.

However, you can do it with Firebird:

SELECT FIRST 5 * FROM ATABLE

>We can do it with this way in interbase.
>Create a TEMP generator.
>Before Execute sql statement set generator's value to 0
>SET GENERATOR TEMP TO 0;
>
>And add this statement to where clause
>(GEN_ID(TEMP,1) < 100)
>
>Example
>
>I want first 100 records from my table
>SET GENERATOR TEMP TO 0;
>SELECT * FROM TABLE1
>WHERE (GEN_ID(TEMP,1) <= 100)
>
>another example
>SET GENERATOR TEMP TO 0;
>SELECT * FROM TABLE1
>WHERE (FIELD1 BETWEEN '01.01.2001' AND '31.12.2001') AND
> (FIELD2 STARTING '100')
>
>if I execute this statement 1500 records will fetch, but I want first 100
>record.
>I can do it with this statement
>SET GENERATOR TEMP TO 0;
>SELECT * FROM TABLE1
>WHERE (FIELD1 BETWEEN '01.01.2001' AND '31.12.2001') AND
> (FIELD2 STARTING '100') and (GEN_ID(TEMP,1) <= 100)

Generators are outside transaction control, so there is nothing to stop any
user from calling the SET GENERATOR TEMP TO 0 statement at any
time. Therefore, there is no way to predict what the generator's value
would be at any given instant in time.

The way to emulate Firebird's SELECT FIRST m SKIP n in a sub-v.6.5
InterBase database, is to write a stored procedure to do the job - one
procedure for each query from which you want some rows:

create procedure GetFirst (Set_size integer, Start_row integer)
as
declare variable size_counter integer;
declare variable skip_counter integer;
declare variable..
declare variable..
(variables for all the cols you want in the output set)
;
begin
size_counter = 0;
skip_counter = 0;
for select blah from atable
order by blah
into :variable1, :variable2,.... do
begin
if (skip_counter <= :start_row) then
skip_counter = :skipcounter + 1;
else
begin
suspend;
counter = :counter + 1;
if (counter = :Set_size) then Exit;
end
end

H.

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________