May
25

How to limit rows returned in a DB2 database

MDBitz - Matthew Denton DB2 Add you comment

Back from an extended vacation and I find myself in the middle of a project needing to export data from a DB2 database. Fortunately I have decent knowledge of SQL and the basic syntax matches up but then I found myself wanting to limit the results returned. Now in T-SQL and MySQL I often would use the, LIMIT, TOP or SET ROWCOUNT commands but they aren’t available in DB2. Instead you use the FETCH FIRST command.

To put things simply to limit the number of rows returned the command is:

FETCH FIRST 10 ROWS ONLY

What this means is that your basic SELECT statement would be entered as:

SELECT * FROM mySchema.myTable WHERE TRAN_TS >= '05/01/2010' FETCH FIRST 10 ROWS ONLY
, , , , ,
Nov
10

Tips & Tricks: How to use a variable in a TOP clause of T-SQL

MDBitz - Matthew Denton T-SQL, Tips & Tricks Add you comment

You may find that when developing Select queries for an Application that you may want to dynamically limit the results returned from a Stored Procedure. Upon first inspection you may think oh lets use a TOP clause with a limit variable passed in when the stored procedure is called.

CREATE PROCEDURE getROWS
    @LIMIT INT
AS
BEGIN
    SELECT TOP @LIMIT *
    FROM TABLE
    ORDER BY modTS DESC
END
GO

Unfortunately Sybase T-SQL does not allow variables in the limit clause. Don’t worry though the ROWCOUNT variable can be paramaterized. This gives us the following procedure that limits the results returned to our desired amount.

CREATE PROCEDURE getROWS
    @LIMIT INT
AS
BEGIN
    SET ROWCOUNT @LIMIT
    SELECT *
    FROM TABLE
    ORDER BY modTS DESC
    SET ROWCOUNT 0
END
GO

That is all there is to it, simply remember to use a ROWCOUNT statement instead of a TOP clause in your stored procedures.

, ,