Tag: create query

Understanding the PARTITION BY Clause in SQL

Posted on by Ian

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

Posted on by Ian

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 →

How to do a DELETE Pass-Through Query in SQL Server

Posted on by Ian

In SQL Server, the OPENQUERY rowset function enables you to execute a pass-through query on a linked server.

OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.

This article presents an example of using OPENQUERY to do an DELETE pass-through query.

Continue reading →

How to do an INSERT Pass-Through Query in SQL Server

Posted on by Ian

In SQL Server, you can use OPENQUERY to execute a pass-through query on a linked server.

OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.

This article presents an example of using OPENQUERY to do an INSERT pass-through query.

Continue reading →

How to do an UPDATE Pass-Through Query in SQL Server

Posted on by Ian

In SQL Server, the OPENQUERY rowset function enables you to execute a pass-through query on a linked server.

OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.

This article presents an example of using OPENQUERY to do an UPDATE pass-through query.

Continue reading →

MongoDB $orderBy

Posted on by Ian

In MongoDB, the $orderBy query modifier sorts the results of a query in ascending or descending order.

$orderBy accepts a document that specifies the field to sort, and the sort order. The sort order can be either 1 for ascending or -1 for descending.

$orderBy has been deprecated in the mongo shell since v3.2. Use the cursor.sort() method instead.

Continue reading →