Tag: create query

How to Write an ORDER BY Clause with Exceptions using SQL

Posted on by Ian

In SQL, the ORDER BY clause is commonly used to order the results of a query. It allows you to select one or more columns to order the results, and in most cases, it’s probably all you need.

But what if you need to make an exception?

What if you want the results to be ordered alphabetically, except for one row? Or several rows?

Or perhaps you simply want to put any NULL values to the end, while ordering the non-NULL results.

Either way, there’s a neat trick you can use that will enable you to do this. And the good part is, it’s simple.

You can cater for all of the above scenarios by adding a CASE expression to your ORDER BY clause.

Continue reading →

How to Return Duplicate Keys from a JSON Document in SQL Server

Posted on by Ian

If you’re trying to extract values from a JSON document, but one or more of the values on the same level have duplicate keys, you might run into problems if you try to extract those values using JSON_QUERY() or JSON_VALUE().

Both of those functions will only return the first value that matches the path. 

Fortunately, there’s another option.

The OPENJSON() function will return all values from any duplicate keys on the same level.

Continue reading →

How to Select a Subset of Columns from a Stored Procedure’s Result Set (T-SQL)

Posted on by Ian

Have you ever run a stored procedure, only to be overwhelmed at the number of columns returned? Maybe you only needed one or two columns, but it presented you with way too many columns for your needs on this particular occasion.

Fortunately, there’s a little trick you can use to retrieve selected columns from a stored procedure. This enables you to get just the columns you need.

And the best part is, it doesn’t involve having to create temporary tables and shuffling the data around.

All you need to do is pass your stored procedure to the OPENROWSET() function.

The same concept can be applied to the OPENQUERY() function.

Continue reading →

SQL SELECT Syntax – Listed by DBMS

Posted on by Ian

This article lists the SQL SELECT syntax, as implemented by various database management systems (DBMSs). The syntax is listed exactly as each vendor has listed it on their website. Click on the applicable link to view more detail about the syntax for a particular vendor.

The DBMSs covered are MySQL, SQL Server, PostgreSQL, and Oracle Database.

Continue reading →

How to Use the Query Designer in SQL Server

Posted on by Ian

To use the Query Designer in SQL Server:

  1. Open a new query by clicking New Query on the toolbar
  2. Open the Query Designer by selecting Query > Design Query in Editor... from the top menu
  3. Add the tables you want to run the query against
  4. Build the criteria for your query then click OK

The query will appear in the query window. To run the query, click ! Execute or press F5

Below are screenshots for the above steps.

Continue reading →