Selecting a Single Row Based on Multiple Criteria From One Column in SQL

1. Overview

In SQL, a common goal is to query data efficiently. For instance, selecting a single row based on multiple criteria from the same column can help us filter data against complex conditions without overstepping column boundaries. As a result, we can easily query for precise data.

In this tutorial, we’ll use examples to provide a practical understanding of single-column filtering in MySQL, PostgreSQL, and SQL Server.

In particular, let’s work with the table Course in the Baeldung University Schema.

2. The WHERE Clause and Limits

In data filtering, checking multiple conditions within a single column is common. For instance, we may need to use a query that selects a row with a column containing specific keywords or matching multiple values.

SQL provides the WHERE clause which can help us in data filtering. In addition, we can combine the universal WHERE with operators when working with multiple criteria for the same column:

• AND – all conditions must be true
• OR – one condition must be at least true
• LIKE – matches patterns in text

If multiple rows meet the criteria, we can specify the filtering conditions along with features like ORDER BY to order the results and LIMIT or TOP to restrict the output to a single row.

So, let’s utilize the Course table which lists course details such as id, name, textbook, credits, is_active, and department_id. Specifically, we aim to select a single row where the textbook column matches multiple criteria.

3. Using MySQL

MySQL provides a flexible way to query data using the WHERE clause in combination with logical operators:

> SELECT * 
FROM Course 
WHERE textbook LIKE '%Numerical%' AND textbook LIKE '%Optimization%' 
ORDER BY id 
LIMIT 1;
+-------+----------------------+------------------------+---------+-----------+---------------+
| id    | name                 | textbook               | credits | is_active | department_id |
+-------+----------------------+------------------------+---------+-----------+---------------+
| MA441 | Advanced Optimizaton | Numerical Optimization |       5 | No        |             5 |
+-------+----------------------+------------------------+---------+-----------+---------------+
1 row in set (0.00 sec)

Here, we also use the LIKE operator for pattern matching:

• SELECT * FROM Course – includes all (*) columns of the Course table in the output
• WHERE textbook LIKE ‘%Numerical%’ – ensures the textbook column consists of the substring Numerical
• AND textbook LIKE ‘%Optimization%’ – specifies the textbook column also needs to include the substring Optimization
• ORDER BY id – sorts the filtered rows by the id column in ascending order
• LIMIT 1 – restricts the output to the first row after sorting the filtered rows using ORDER BY

So, this query scans the Course table and displays the row that meets both criteria for the given column.

The methods in this section, using the WHERE clause in combination with logical operators such as AND and LIKE, aren’t specific to MySQL. These techniques are supported in most SQL systems such as PostgreSQL and SQL Server.

4. Using PostgreSQL

PostgreSQL also supports the use of text pattern matching and logical operators.

For example, let’s construct a query using the OR operator:

> SELECT * FROM Course WHERE textbook LIKE '%Numerical%' OR textbook LIKE '%Optimization%';
  id   |            name             |            textbook             | credits | is_active | department_id 
-------+-----------------------------+---------------------------------+---------+-----------+---------------
 MA141 | Introduction to Optimizaton | An Introduction to Optimization |       7 | Yes       |             5
 MA241 | Convex Optimizaton          | An Introduction to Optimization |       7 | No        |             5
 MA441 | Advanced Optimizaton        | Numerical Optimization          |       5 | No        |             5
(3 rows)

Above, the query outputs three rows. It filters the rows where the textbook column contains either the substring Numerical or the substring Optimization.

However, we can modify the query to ensure we get a single row:

> SELECT * 
FROM Course 
WHERE textbook LIKE '%Numerical%' OR textbook LIKE '%Optimization%'
ORDER BY id
LIMIT 1;
  id   |            name             |            textbook             | credits | is_active | department_id 
-------+-----------------------------+---------------------------------+---------+-----------+---------------
 MA141 | Introduction to Optimizaton | An Introduction to Optimization |       7 | Yes       |             5
(1 row)

This query selects a row from the Course table where the textbook matches either of the two criteria (Numerical or Optimization). It prioritizes rows based on the id column and returns only the top result.

Additionally, we can modify this query to use regular expressions:

> SELECT * 
FROM Course 
WHERE textbook ~* 'Numerical|Optimization' 
ORDER BY id 
LIMIT 1;

Let’s understand the modification textbook ~* ‘Numerical|Optimization’:

• ~* – this operator performs case-insensitive matching with a regular expression
• ‘Numerical|Optimization’ – this pattern ensures the textbook column matches if it contains either Numerical or Optimization

Rows that differ in case, like NUMERICAL optimization, would also match due to the case-insensitivity of ~*.

To be clear, ~* for case-insensitive regex is unique to PostgreSQL whereas the WHERE clause and logical operators are applicable in most SQL systems like PostgreSQL.

5. Using SQL Server

Lastly, let’s demonstrate selecting a single row based on multiple criteria from the textbook column in SQL Server:

> SELECT TOP 1 * 
FROM Course 
WHERE textbook LIKE 'Signals%Oppenheim' 
ORDER BY id;
  id   |        name        |             textbook             | credits | is_active | department_id 
-------+--------------------+----------------------------------+---------+-----------+---------------
 EC121 | Signal and Systems | Signals and Systems by Oppenheim |       7 | Yes       |             2
(1 row)

The query works in a manner similar to the previous examples:

• TOP 1 – restricts the output to the first row after sorting the filtered rows using ORDER BY
• LIKE ‘Signals%Oppenheim’ – matches a text that begins with Signals and ends with Oppenheim
• % – represents a wildcard that matches any sequence of characters in between, in this case, the middle part and Systems by

The WHERE clause with LIKE is a universal SQL feature. However, the TOP clause is specific to SQL Server.

6. Conclusion

In this article, we explored how to select a single row based on multiple criteria from one column in SQL. To be specific, we covered MySQL, PostgreSQL, and SQL Server.

We leveraged logical operators, pattern matching, regular expressions, and query features like ORDER BY and LIMIT. Hence, we can now handle complex queries in different database environments and optimize data filtering.