what is

Understanding the PARTITION BY Clause in SQL

by

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.

Read more

Understanding the WINDOW Clause in MySQL

by

In MySQL, the WINDOW clause is an optional clause that we can use to create a named window. The named window can then be referred to from a window function.

Many SQL developers define their window functions directly in the OVER clause. But that’s not the only way to do it. We can also define them in a WINDOW clause, and then refer to them in the OVER clause.

When we define the window function in a WINDOW clause, we name it. When we do this, we can refer to that name from the OVER clause. This eliminates the need to include the definition directly inside the OVER clause.

Read more

Introduction to the OVER Clause in SQL

by

When running SQL database queries, sometimes we need to use a window function in order to get the results we’re looking for. A window function is an aggregate-like function that enables partitioning and ordering of data within a result set.

The OVER clause is what enables us to create a window function.

The examples below demonstrate how we can incorporate the OVER clause in our SQL queries.

Read more

Understanding the sys.quote_identifier() Function in MySQL

by

In MySQL, we can use the sys.quote_identifier() function to quote a string to produce a result that can be used as an identifier in a SQL statement.

The string is returned enclosed by backticks (`), with each instance of a backtick doubled.

This function can be handy when a value to be used as an identifier is a reserved word or contains backtick characters.

Read more

Understanding Window Functions in SQL

by

Window functions can be a useful tool when writing SQL queries. They allow us to include aggregate data across multiple rows without getting those pesky errors that sometimes occur when we try to use an aggregate function in the wrong way.

In this article, I aim to provide a simple overview of window functions and how they can be used to provide a more useful result set when running SQL queries.

Read more

SERIAL vs AUTO_INCREMENT in MySQL

by

If you’ve ever encountered a SERIAL column in a MySQL database, you might’ve wondered how it differs from an AUTO_INCREMENT column. After all, they both generate values that automatically increment?

Read on to find out what the difference is between SERIAL and AUTO_INCREMENT in MySQL.

Read more

Using the sys.format_statement() Function in MySQL to Truncate a SQL Statement

by

In MySQL, the sys.format_statement() system function reduces the length of a given string/SQL statement to the length stored in the statement_truncate_len configuration option.

This function can be handy for truncating potentially long SQL statements retrieved from Performance Schema tables into a more suitable length (64 characters by default).

Read more

Understanding the PS_THREAD_ID() Function in MySQL

by

In MySQL, we can use the PS_THREAD_ID() function to get the Performance Schema thread ID assigned to a given connection ID. This can be useful whenever we use a function that takes the thread ID as its parameter, but we only know the connection ID.

If no thread ID exists for the connection, then NULL is returned.

The built-in PS_THREAD_ID() function does the same thing as the now deprecated sys.ps_thread_id() function. Therefore, we can call the function without needing to qualify it with sys or making sys our current schema.

Read more