SQL Pocket Guide, 4th Edition

Chapter 10. How Do I…?

This chapter is intended to be a quick reference for frequently asked SQL questions that combine multiple concepts:

• Find the rows containing duplicate values

• Select rows with the max value for another column

• Concatenate text from multiple fields into a single field

• Find all tables containing a specific column name

• Update a table where the ID matches another table

Find the Rows Containing Duplicate Values

The following table lists seven types of teas and the temperatures they should be steeped at. Note that there are two sets of duplicate tea/temperature values, which are in bold.

SELECT * FROM teas;

+----+--------+-------------+
| id | tea    | temperature |
+----+--------+-------------+
|  1 | green  |         170 |
|  2 | black  |         200 |
|  3 | black  |         200 |
|  4 | herbal |         212 |
|  5 | herbal |         212 |
|  6 | herbal |         210 |
|  7 | oolong |         185 |
+----+--------+-------------+

This section covers two different scenarios:

• Return all unique tea/temperature combinations

• Return only the rows with duplicate tea/temperature values

Return All Unique Combinations

To exclude duplicate values and return only the unique rows of a table, use the DISTINCT keyword.

SELECT DISTINCT tea, temperature
FROM teas;

+--------+-------------+
| tea    | temperature |
+--------+-------------+
| green  |         170 |
| black  |         200 |
| herbal |         212 |
| herbal |         210 |
| oolong |         185 |
+--------+-------------+

Potential extensions

To return the number of unique rows in a table, use the COUNT ...