The SQL DISTINCT clause allows us to remove redundant duplicate rows from our query results. For example if two rows contain the same values, then only one of them is returned.
But how does DISTINCT handle NULL values?
Let’s find out.
Continue readingIn statistics, the mode is the most commonly occurring value in a data set. When using SQL databases, we can easily run a SQL query to find out the mode of a given column.
Below are several options we can use to calculate the mode in SQL.
Continue readingMany SQL databases have a window function called NTILE() function that divides a rowset or partition into a given number of groups (buckets). The function typically returns the bucket number of the current row within its partition.
Most of the major relational database management systems (RDBMSs) allow for a DISTINCT clause to be included in our SQL queries.
We use the DISTINCT keyword to return only unique rows. It eliminates duplicates from the results. If we have two or more rows with exactly the same data, we’ll only see one row in the results.
The LAG() function is a window function that’s available in many SQL databases. It returns the value of a given expression from the row that lags (precedes) the current row by a given number of rows within its partition.
In other words, the LAG() function returns a value from a previous row.
Many relational database management systems (RDBMSs) have a LEAD() function that allows us to retrieve a value from a following row.
The SQL LEAD() function returns the value of a given expression from the row that leads (follows) the current row by a given number of rows within its partition.
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.