Overview
This lecture introduces the Oracle SQL ROWNUM function, demonstrating how to use it to rank query results, especially for summarizing and ordering stock buy volumes.
Introduction to ROWNUM
- The
ROWNUM function assigns a unique number to each row in a SQL query result.
ROWNUM is useful for ranking and identifying positions of rows in ordered data sets.
Example: Summing Buy Volumes by Stock Ticker
- Query selects stock tickers and sum of quantities for buy orders from the
ordertransactions table.
- Uses
GROUP BY on the stock ticker to aggregate total buy volumes.
- Applies
ORDER BY to display results in decreasing order of total buy volume.
Adding ROWNUM for Ranking
- The core query is nested as a subquery to enable further selection.
- In the outer query, select the ticker symbol, buy volume, and
ROWNUM AS rank.
- Running this displays the rank for each ticker based on buy volume ordering.
Filtering Top N Results Using ROWNUM
- To limit the output to only the top N ranked results, add a
WHERE clause: WHERE ROWNUM <= N.
- Example:
WHERE ROWNUM <= 5 shows only the top 5 companies by buy volume.
Use Cases and Benefits
ROWNUM quickly identifies rank positions for rows in ordered reports.- Useful for generating concise reports, such as showing only the highest-ranking items.
Key Terms & Definitions
- ROWNUM — Oracle function assigning sequential numbers to rows in a query result.
- Aggregate Function (SUM) — Calculates the total value for a group of rows.
- GROUP BY — SQL clause grouping rows to apply aggregate functions.
- ORDER BY — Sorts query results in ascending or descending order.
- Subquery/Nested Query — A SELECT statement placed inside another SELECT for advanced filtering or calculation.
Action Items / Next Steps
- Practice creating nested SQL queries using
ROWNUM to rank results.
- Prepare for next lecture covering the
EXTRACT function and advanced GROUP BY variants.
- Review how to use subqueries for organizing and manipulating query output.