How do I get the top 15 rows 1

[KFurstenberg]

I have a situation, using UDB, in which I am attempting to return only the top 15. I'm getting all the rows since the COUNT is always a value of 1 (HAVING clause isn't making any difference). This is how the SQL was originally written in order to accomplish this:

' DB2
Set conn = Server.CreateObject("ADODB.Connection&quot
conn.Open "DSN=DFOC01;UID=********;PWD=********"
SQLStmt0 = "SELECT FOCP.TFOC_BATCH_CONTROL.VALUATION_DATE AS VALUATION_DATE, "
SQLStmt0 = SQLStmt0 + " YEAR(FOCP.TFOC_BATCH_CONTROL.VALUATION_DATE) AS YEAR, "
SQLStmt0 = SQLStmt0 + " MONTH(FOCP.TFOC_BATCH_CONTROL.VALUATION_DATE) AS MONTH, "
SQLStmt0 = SQLStmt0 + " DAY(FOCP.TFOC_BATCH_CONTROL.VALUATION_DATE) AS DAY "
SQLStmt0 = SQLStmt0 + " FROM FOCP.TFOC_BATCH_CONTROL "
SQLStmt0 = SQLStmt0 + &quot; GROUP BY VALUATION_DATE HAVING COUNT(*) < 16&quot;
SQLStmt0 = SQLStmt0 + &quot; ORDER BY 1 DESC&quot;
Set rs0 = conn.Execute(SQLStmt0)

Any suggestions as to how I can retrieve just the top 15? This is being executed within an ASP. The primary key in the table is VALUATION_DATE. TIA.

 

[swampBoogie]

Depends on which DBMS you are using. Although the current SQL standard has support for this type of queries it is not implemented by all vendors but they rather have their own proprietary solutions.

The ANSI SQL solution is to use the row_number function (Example adopted from DB2.)

SQLStmt0 = &quot;select * from (SELECT row_number() over (order by FOCP.TFOC_BATCH_CONTROL.VALUATION_DATEvaluation_date desc) as rowno,&quot;
SQLStmt0 = SQLStmt0 & &quot;FOCP.TFOC_BATCH_CONTROL.VALUATION_DATE AS VALUATION_DATE, &quot;
SQLStmt0 = SQLStmt0 + &quot; YEAR(FOCP.TFOC_BATCH_CONTROL.VALUATION_DATE) AS YEAR, &quot;
SQLStmt0 = SQLStmt0 + &quot; MONTH(FOCP.TFOC_BATCH_CONTROL.VALUATION_DATE) AS MONTH, &quot;
SQLStmt0 = SQLStmt0 + &quot; DAY(FOCP.TFOC_BATCH_CONTROL.VALUATION_DATE) AS DAY &quot;
SQLStmt0 = SQLStmt0 + &quot; FROM FOCP.TFOC_BATCH_CONTROL &quot;
SQLStmt0 = SQLStmt0 + &quot; ) as dt where rowno <= 15&quot;
SQLStmt0 = SQLStmt0 + &quot; ORDER BY 2 DESC&quot;

 

[KFurstenberg]

Thank you, your solution is exactly what we needed.