Some of the major relational database management systems (RDBMSs) have a LIMIT clause that enables us to reduce the number of rows returned by a query.
The way it works is that we provide the number of rows we want to be returned by the query. We can also provide an offset to specify which row to start the count from.
Continue readingMany 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.
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 readingSome 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.
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 readingSome 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.
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.
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 readingMost RDBMSs have functions that make it easy to calculate the median value from a column in our queries.
Below are three SQL functions we can use to calculate the median value in a column.
Continue readingSome 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 reading