One of the questions that I got was: “Can you define in the SQL itself a maximum number of retrieved rows (“TOP” in SQL Server, “rownum” in oracle)?” Let me start by saying that I love it when people ask this question. Why? Because for the longest time I would come across code where a programmer would use the simplest SQL to fetch out a huge result set, sort it in the application to find the top 10 rows and dump the rest. Every decent DBMS out there lets you do it right; there is absolutely no excuse for this type of sillines. I am being kind here.
For example, in Microsoft SQL Server you would use TOP:
SELECT TOP 10 column FROM table
MySQL and PostgreSQL SQL would use LIMIT like so:
SELECT column FROM table LIMIT 10
PostgreSQL v8.3 and later can also use this more standard SQL:
SELECT column FROM table FETCH FIRST 10 ROWS ONLY
An Oracle programmer would write
SELECT column FROM table WHERE ROWNUM <= 10
In Sybase, you would set rowcount
SET rowcount 10
SELECT column FROM table
DB2, as you would expect, also has special SQL syntax to limit the number of rows returned by a query. You can simply append FETCH FIRST n ROWS ONLY to you query and you are set. By the way, this is SQL:2008 standard but I doubt many people care.
SELECT column FROM table FETCH FIRST 10 ROWS ONLY
Read more: FreeDB2.com
Posts
