Many RDBMSs include both a rank() and a dense_rank() function in their list of window function offerings. At first glance, these functions might appear to do the same thing. However, there’s one important difference between these functions, and you will definitely need to be aware of this difference when choosing which function to use.
Tag: what is
Understanding the FETCH Clause in Oracle
Oracle Database has a FETCH clause that allows us to limit the number of rows returned by a SELECT statement.
We can use this clause to specify the exact number of rows or the percentage of rows that a query should return. We can also specify an offset for which to start.
Continue readingUsing the ROW_NUMBER() Function to get Row Numbers in SQL
The SQL ROW_NUMBER() function is a window function that assigns and returns a row number of each row in a query partition or result set. Numbering starts at 1 and increments sequentially.
This enables us to add a “row number” column to our queries.
Continue readingAn Introduction to the MEDIAN() Function in SQL
Some relational database management systems (RDBMSs) have a MEDIAN() function that calculates the median value within a range of values.
It returns the middle value or an interpolated value that would be the middle value once the values are sorted.
The SQL MEDIAN() function typically operates on numeric expressions, but depending on the RDBMS, may also operate on other data types, such as datetime expressions.
Depending on the RDBMS, the MEDIAN() function can be used as a window function, as an aggregate function, or as both.
Understanding the MODE() Function in SQL
Some SQL relational database management systems (RDBMSs) have a MODE() function that returns the most frequently occurring value from all row values in a column.
The mode is the value that appears most frequently in a data set.
Continue readingIntroduction to the PERCENTILE_DISC() Function in SQL
Some SQL databases have a PERCENTILE_DISC() function that calculates a percentile based on a discrete distribution of a range of column values.
We specify the percentile to use when we call the function.
Depending on the DBMS, PERCENTILE_DISC() can be used as a window function, as an aggregate function, or as both.
The PERCENTILE_DISC() function always returns a value from the underlying data. This is in contrast to the PERCENTILE_CONT() function, which can interpolate between adjacent values to return a value that’s not in the underlying data.
Overview of the PERCENTILE_CONT() Function in SQL
Some relational database management systems (RDBMSs) have a PERCENTILE_CONT() function that calculates a percentile based on a continuous distribution across a range of column values.
We specify the percentile to use when we call the function.
Depending on the RDBMS, PERCENTILE_CONT() can be used as a window function or an aggregate function, or as both.
An Introduction to the PERCENT_RANK() Function in SQL
Many relational database management systems (RDBMSs) provide a window function called PERCENT_RANK() that returns the relative rank of a row within a group of rows.
The relative rank is expressed as a value between 0 and 1.
Continue readingUnderstanding the NTH_VALUE() Function in SQL
Some SQL databases have a window function called NTH_VALUE() that allows us to get a value from a given row in the window frame, based on the row number.
More specifically, the function returns the value of a given expression from the from the N-th row of the window frame, where N is a number that we specify when calling the function.
Continue readingUnderstanding the LAST_VALUE() Function in SQL
In SQL databases, LAST_VALUE() is a window function that returns the last value in an ordered set of values. It allows us to get a value from the last row of a query result set or partition.
You may need to explicitly set the window frame if you want LAST_VALUE() to return the actual last value from the partition or result set. That’s because in many/most DBMSs, the default window frame ends with the current row.