Many RDBMSs have a LIMIT clause that allows us to limit the rows returned by a query to a specified number. SQL Server doesn’t have a LIMIT clause but it does have a TOP() function that allows us to do the same thing.
But Oracle Database has neither.
Fortunately, Oracle does have a number of options that allow us to get the same result.
Continue readingMost 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.
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.
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 readingOracle Database has a FETCH clause that allows us to limit the number of rows returned by a SELECT statement.
We can use this clause to specify the exact number of rows or the percentage of rows that a query should return. We can also specify an offset for which to start.
Continue readingSometimes 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.
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 readingIn 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.
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.
MongoDB provides a few ways to sort documents in a particular order. Each of these allow us to order the documents in ascending or descending order.
There’s also the option to order the results of a $text search – sorting by the computed textScore metadata in descending order.
Here are 3 ways to sort documents in MongoDB.
Continue reading