Show me the different ways to say 'give me the top ten rows' across the common SQL dialects.
There are five spellings, and only two of them are worth using.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL
);
INSERT INTO products (id, name, unit_price) VALUES
(1, 'Notebook', 4.50),
(2, 'Desk lamp', 24.00),
(3, 'Pen', 1.20);
-- The way that MySQL, PostgreSQL and SQLite all use.
SELECT id, name FROM products ORDER BY unit_price DESC LIMIT 10;
-- The standard way. PostgreSQL and SQL Server both use it.
SELECT id, name FROM products ORDER BY unit_price DESC FETCH FIRST 10 ROWS ONLY;
-- The way that SQL Server has always used.
SELECT TOP 10 id, name FROM products ORDER BY unit_price DESC;
-- The older Oracle way, where the limit goes in a wrapper.
SELECT * FROM (
SELECT id, name FROM products ORDER BY unit_price DESC
) WHERE ROWNUM <= 10;
So which should you use? Two rules.
The first one is that you should know the spelling of the engine you are actually using, and that is the only one that matters for a single project.
The second one is that if your code has to run on more than one engine, then you should use the standard form, which is FETCH FIRST n ROWS ONLY, and that every modern engine supports. The older TOP form is not in the standard, and the ROWNUM form is not in the standard and does not even mean the same thing, because it is applied after the sort.
There is one more form that you will meet, and that is the percentage one. It is written as TOP 10 PERCENT in SQL Server and as FETCH FIRST 10 PERCENT ROWS ONLY in the standard. It is not something you need often, and it has the same problem as the ROWNUM form, because the count is worked out after the sorting.
The next two questions in this topic look at a real query and at the speed of these forms.