How to Force a Guaranteed Minimum Value When Selecting a Column in SQL Server

Suppose you’re trying to query column, but you need to set a minimum value to be returned, even if the column contains values that are less than that minimum. For example, you want a minimum value of 50 to be returned, even if the column contains values that are less than 50.

We can use the GREATEST() function to build such a query.

Continue reading

Understanding the DISTINCT ON Option in PostgreSQL

Most major RDBMSs support the DISTINCT clause, which allows us to get unique – or “distinct” – rows from our SQL queries. But PostgreSQL’s implementation of this clause has an extra option that most other RDBMSs don’t include.

PostgreSQL allows us to include the ON() option when using DISTINCT. This enables us to specify exactly which columns should be evaluated by the DISTINCT modifier, while simultaneously allowing us to return columns that aren’t evaluated.

Continue reading

Understanding the OFFSET Clause in Oracle

In Oracle Database, we can use the OFFSET clause to make a SELECT statement skip a certain number of rows in its result set.

The OFFSET clause is defined as part of the FETCH row limiting clause, which allows us to limit the number of rows returned by a query.

We can therefore use both OFFSET and FETCH to limit the output to just the specified number or percentage of rows, at a specified offset.

Continue reading

Understanding the PARTITION BY Clause in SQL

Sometimes when we run SQL queries we might want to group the results by a given column. A common way to do this is with the GROUP BY clause.

But sometimes the GROUP BY clause isn’t enough.

Sometimes we want the results presented in a way that the GROUP BY clause doesn’t cater for. The GROUP BY is perfect if we want to collapse groups of rows into a single row, so that we can get an aggregate value for each set of collapsed rows. But sometimes we don’t want this. Sometimes we want to see all of the rows, as well as the aggregate values.

This is where the PARTITION BY clause comes in.

Continue reading

Get the Number of Rows Affected by Previous SQL Statement

Some RDBMSs provide an easy way for us to find out how many rows were affected by the last SQL statement. This can be handy when running INSERT, UPDATE, or DELETE statements.

The method used depends on the DBMS we’re using. Below, I look at how some of the major DBMSs implement this functionality.

Continue reading