Mainframe, MVS and zOS Discussion

How to get top 5 rows from table thru SQL?

Hello.

It would depend upon what you mean by "top 5 rows" and through what method you are trying to access the data.

If you are entering SQL through something like SPUFI, then I'm not aware of a way to limit the number of rows returned purely through the clauses on a "SELECT" statement, although SPUFI does have the "max select lines" option which can be used to limit the amount of data shown in the output dataset.

If you are coding this within a program then it's simply a matter of only retrieving five rows from the cursor. There is an "OPTIMIZE FOR n ROWS" clause that can be added to a cursor declaration which lets DB2 know that you're not going to be retrieving more than "n" rows, plus the "FETCH" statement has a "FOR n ROWS" clause which allows an array of values to be returned.

Hi,

Thanks for the reply. I want to test the query in SPUFI and QMF and later use the query in COBOL program.

I don't think QMF has any sort of limiter on rows retrieved, but any given query will give you the same result no matter where you call it from. I'd suggest an "ORDER BY" or "GROUP BY" clause to ensure the data is returned in the order you expect. Updates to the table will alter its physical organisation; without an ordering clause, DB2 will return whatever it thinks is optimal given the current state of the table, so the results could look quite random if changes to the data are regularly being made.

DB2 on mainframes, you can use the FETCH FIRST clause to retrieve a specific number of rows from a table. Here's an example of how you can get the top 5 rows from a table in SQL:
If you are looking for data based on a specific column, an "ORDER BY" or "GROUP BY" clause should be used, as zum13 said.