How to Do SQL Select Top N ... in As400

How to do SQL select top N ... in AS400

Select col1,col2
from 
as400table
where col1='filter'
order by col1
fetch first N row only

Remember to set an ORDER BY clause, because DB2 does not guarantee that the rows returned by FETCH FIRST N ROW ONLY are always the same N.

Getting top n to n rows from db2

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061832.html

db2 "select row_number() over(order by tabschema, tabname)
,    tabschema::char(10), tabname::char(30)
from syscat.tables
order by tabschema, tabname 
offset 10 rows 
fetch first 10 rows only"

1                    2          3                             
-------------------- ---------- ------------------------------
                  11 SYSCAT     COLCHECKS                     
                  12 SYSCAT     COLDIST                       
                  13 SYSCAT     COLGROUPCOLS                  
                  14 SYSCAT     COLGROUPDIST                  
                  15 SYSCAT     COLGROUPDISTCOUNTS            
                  16 SYSCAT     COLGROUPS                     
                  17 SYSCAT     COLIDENTATTRIBUTES            
                  18 SYSCAT     COLLATIONS                    
                  19 SYSCAT     COLOPTIONS                    
                  20 SYSCAT     COLUMNS                       

  10 record(s) selected.

how to perform 'SELECT TOP X FROM TABLE' type queries with DB2 / dashDB

You can achieve this query using the FETCH FIRST x ROWS ONLY statement, E.g.

SELECT * FROM customers FETCH FIRST 1 ROWS ONLY

Select Top rows and Order by NEWID() in DB2

Ordering is not a good idea, especially for large tables.

We have an ability to use built-in Db2 for LUW feature of subselect - tablesample-clause.

BERNOULLI

BERNOULLI sampling considers each row individually. It includes each row in the sample with probability P/100 (where P is the value of
numeric-expression1), and excludes each row with probability 1 -
P/100, independently of the other rows. So if the numeric-expression1
evaluated to the value 10, representing a ten percent sample, each row
would be included with probability 0.1, and excluded with probability
0.9.

SYSTEM

SYSTEM sampling permits the database manager to determine the most efficient manner in which to perform the sampling. In most cases,
SYSTEM sampling applied to a table-name means that each page of
table-name is included in the sample with probability P/100, and
excluded with probability 1 - P/100. All rows on each page that is
included qualify for the sample.

Example:

SELECT * 
FROM MYTABLE TABLESAMPLE SYSTEM (0.1) 
FETCH FIRST 10 ROWS ONLY;

This feature doesn't exist in DB2 for IBM i/iSeries/OS400. Use the order by rand() fetch first 10 rows clause for this platform instead.

How to select N characters from an AS400/DB2 Database Column

You should store dates as, well, dates and not strings. But, you can do what you want as:

select substr(columndata, 1, 4) as Year, substr(columndata, 5, 2) as Month
. . .

SQL Query Limit for DB2 AS/400 Version 4

There is no dbms support for this operation, check Version 4 DB2 UDB for AS/400 SQL Reference: No Limit, Top, First, ... reserved words.

You can try to limit rows via where clause, where sequence between 100 and 200. But this is an unreal scenario.

First work around is via cursor:

DECLARE ITERROWS INTEGER;
...
SET ITERROWS = 0;
DO WHILE (SUBSTR(SQLSTATE,1,2) = '00' and ITERROWS < 100
DO
    ...
    SET ITERROWS = ITERROWS + 1;

second one, in your middleware language.

I hope someone post a clever workaround, but, in my opinion, they are not.

DB2 SQL SELECT top 1 group with join

Almost got it. With any aggregates, Max(), Min(), Avg(), Count(), Sum(), a GROUP BY clause is required listing the "level" columns which usually are indicator fields (names, categories, types).

Also, you will leave out a level column that you are trying to run an aggregate, here being POORDT. Finally, you will not be able to ORDER BY the very aggregated field but only the group fields listed.

SELECT PO180M2.PHPPN AS PartNo, (PO180M2.PHVNPD || ' ' || PO180M2.PHVNP2) AS PartDesc, 
       PO180M2.PHUNCT AS UnitCost, Max(PO180M1.POORDT) As MaxPOORDT
FROM PO180M1 
INNER JOIN PO180M2 ON PO180M1.POORNO = PO180M2.PHORNO 
GROUP BY PO180M2.PHPPN, (PO180M2.PHVNPD || ' ' || PO180M2.PHVNP2), PO180M2.PHUNCT
ORDER BY PartNo

By the way, I noticed you used the older syntax of table joins listing tables in FROM clause with a WHERE clause. I changed it to an INNER JOIN with ON which I believe is compliant with DB2.

Fetch first row only with IN operation in DB2

So you want latest per user? Use ROW_NUMBER() :

SELECT * FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.user_id ORDER BY t.date DESC) as rnk
    FROM sale t
    WHERE t.user_id IN(1,2)) s
WHERE s.rnk = 1

---

Related Topics