create query

Fix Error “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions…” in SQL Server (Error 1033)

by

If you’re getting an error that reads “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified” in SQL Server, it appears that you’re using the ORDER BY clause in a place that it must also be accompanied by a TOP, OFFSET or FOR XML clause, but you’re not including one of those clauses.

To fix this issue, be sure to include one of those clauses in your query if you need to use the ORDER BY clause.

Read more

Fix “The correlation name … is specified multiple times in a FROM clause” in SQL Server (Error 1011)

by

If you’re getting an error that reads something like “The correlation name ‘a’ is specified multiple times in a FROM clause’” in SQL Server, it could be that you’re doing a join on two tables, but you’re assigning them the same aliases.

Each table’s alias must be unique.

To fix this issue, be sure to use unique table aliases in your query.

Read more

Prepared Statements in PostgreSQL: A Complete Guide

by

Prepared statements are a feature in PostgreSQL that can improve performance and security when executing repetitive SQL queries.

By creating a prepared statement, the server can parse and plan the query once and reuse it multiple times, leading to faster execution. Prepared statements also help in preventing SQL injection attacks by safely handling user input.

In this article, we’ll explore prepared statements in PostgreSQL, discuss how to create and use them, and cover other important considerations such as query planning and cleaning up statements.

Read more

3 Quick Examples of SQL Subqueries in the WHERE Clause

by

SQL subqueries are like little helpers that fetch data for our main query. They’re super handy when we need to filter our results based on some other data in our database.

Probably the most common placement of a subquery in SQL is in the WHERE clause. Placing a subquery in the WHERE clause allows us to compare a column against a set of rows that match a certain criteria.

Let’s look at three quick examples of subqueries in the WHERE clause.

Read more

Fix “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause” in SQL Server (Error 144)

by

If you’re getting an error that reads something like “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.” in SQL Server, it looks like you’re trying to use either an aggregate function or a subquery in the GROUP BY clause.

We can’t use aggregates or subqueries in the GROUP BY clause.

To fix this issue, remove any aggregate functions or subqueries from your GROUP BY clause.

Read more

Understanding the RID Lookup in SQL Server: How It Affects Performance

by

If you’ve spent any time working with heaps in SQL Server, you may have seen the RID lookup in the query plan. And if you haven’t, chances are it’s because you either didn’t look at the query plan, or the queries used full table scans.

In any case, it pays to understand what a RID lookup is and how it affects query performance. If we ever see RID lookup in the query plan, it could be a good opportunity to see if we can make changes that will improve performance.

Read more

Aggregating Data in SQL with the GROUP BY and HAVING Clauses

by

When writing queries in SQL, we sometimes need to summarize data based on specific criteria. Fortunately SQL makes this easy to achieve with the GROUP BY and HAVING clauses. These two clauses allow us to aggregate data and filter the results based on group conditions.

More specifically, GROUP BY allows us to group the results, and HAVING is an optional clause that we can use to filter the results based on certain conditions.

Below is an example of where we can use these two clauses to aggregate data and filter it based on certain criteria.

Read more