How do I select all the columns from a table, plus additional columns like ROWNUM?

sql

window-functions

performance-tuning

best-practices

byAnton Shumikhin·Dec 28, 2024

Fetch every column along with row numbers in Oracle as follows:

SELECT ROWNUM, t.* FROM your_table t;

Here, * is a shorthand for all columns and ROWNUM indicates the row position. In SQL Server, PostgreSQL, or MySQL, replace ROWNUM with ROW_NUMBER() OVER (ORDER BY some_column):

SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) AS row_number FROM your_table;

Don't forget to replace some_column with a column from your table to define the row order.

The ABCs of row numbers in SQL

Handling row numbers in SQL varies across databases. In Oracle, it's ROWNUM. On the other hand, other SQL databases like PostgreSQL and MySQL use a window function: ROW_NUMBER() OVER (ORDER BY ...).

One crucial Oracle-specific tip: always following ROWNUM with the table qualifier (t.*), cementing its position and avoiding any unexpected outcomes.

Potential pitfalls and their remedies

When dealing with row numbers in SQL, here are some barriers you might hit and how to tackle them:

Execution Order: Oracle assigns ROWNUM before sorting. If sorting is a necessity, consider using a subquery.

SELECT * FROM (SELECT ROWNUM AS row_number, t.* FROM your_table t ORDER BY t.some_column) sub_query; 
-- If this miner can dig SQL orders, got any more hidden gold? :)

Handling Large Datasets: Untamed usage of row_number() on colossal tables could decimate performance. Reel in your usage, or utilize parallel execution in the circumstances your Oracle setup allows.
Column Confusion: Amidst frenzied joins, always tack the table alias with any column to ward off the infamous "ambiguous column name" error.

SQL for easy reading and maintenance

Keep your SQL from becoming a spaghetti code with simple practices:

Alias Power: Use table aliases not just for brevity, but for readability especially when multiple tables come into play.
Declaring Wildcards: Preface your wildcard * with the table name or alias (say t.*) to make it clear as day where the columns originate.
Clean SQL: Carve your code like a sculpture with rightful indentation and line breaks, akin to neatly arranging books on a shelf.

Window functions: Not just for the view!

Window functions in SQL can do more than just deliver a spectacular view of your data:

Ranking: Crown your data with RANK(), DENSE_RANK(), or ROW_NUMBER() for sorting rows into choicest categories.
Detail-rich Aggregates: Reach for functions like SUM() across rows without losing sight of the detail rows.
Navigational Knights: Employ LAG() and LEAD() to traverse to rows' prior or subsequent data.

Turbocharge your queries

Performance is the heart of SQL. Dish out near-instantaneous responses with a deeper understanding of database engines:

Leverage Indexes: Indexes on columns involved in ORDER BY within ROW_NUMBER() can give your query a significant performance boost.
Batch Over Bulk: For behemoth datasets, opt for batch processing or cursor operations, instead of introducing row numbers to the whole dataset.
Smart Subqueries: Framing your subqueries strategically can nudge Oracle's optimizer to work marvelously, promising wide performance improvements for complex queries.