Summary  
This chapter explains how to sort query results by multiple columns in SQL by listing column names separated by commas, where the first column has primary priority and subsequent columns define secondary ordering.

General domain of usage  
Database querying

We can also sort by multiple columns. We need to specify the column names **separated by commas**. 

The first column specified in the sorting will have **priority**, and then the sorting will proceed according to the second column.

The syntax will look like this:

```sql
SELECT columns 
FROM table 
ORDER BY column_with_priority, column2, ...
```

Let’s look at the example that retrieves three columns and sorts the results by two of them: first by `population` and then by `capital`:

SELECT ID, population, capital 
FROM country 
ORDER BY population, capital;

import unittest
import psycopg2
import pandas as pd

class TestTask(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.connection = psycopg2.connect(
            host="localhost",
            user="agent",
            password="qwerty",
            dbname="testdb",
            sslmode="disable"
        )
        cls.cursor = cls.connection.cursor()

    def test_selects_correct_columns_and_order(self):
        """Check that the query selects exactly the columns: continent, region, population, capital in that order."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        colnames = [desc[0] for desc in self.cursor.description]
        expected = ["continent", "region", "population", "capital"]
        condition = colnames == expected
        _dynamic_test(
            self,
            condition,
            "Query selects columns in correct order.",
            f"Expected columns {expected}, got {colnames}."
        )

    def test_order_by_continent_population(self):
        """Check that the result is ordered first by continent, then by population (ascending)."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        rows = self.cursor.fetchall()
        if not rows or len(rows) < 2:
            _dynamic_test(
                self,
                True,
                "Result has less than 2 rows, ordering cannot be tested.",
                ""
            )
            return
        df = pd.DataFrame(rows, columns=[desc[0] for desc in self.cursor.description])
        # Sort with pandas to expected order
        expected_df = df.sort_values(by=["continent", "population"], ascending=[True, True], na_position='last').reset_index(drop=True)
        user_df = df.reset_index(drop=True)
        condition = expected_df[["continent", "population"]].equals(user_df[["continent", "population"]])
        _dynamic_test(
            self,
            condition,
            "Rows are ordered by continent and then by population.",
            "Rows are not ordered by continent and then by population."
        )

    def test_selects_from_country_table(self):
        """Check that the query selects from the country table (by verifying result matches expected rows from country table)."""
        with open("query.sql", "r") as file:
            query = file.read()
        self.cursor.execute(query)
        user_rows = self.cursor.fetchall()
        self.cursor.execute("SELECT continent, region, population, capital FROM country ORDER BY continent, population;")
        expected_rows = self.cursor.fetchall()
        condition = user_rows == expected_rows
        _dynamic_test(
            self,
            condition,
            "Query retrieves correct rows from country table.",
            "Query does not retrieve correct rows from country table."
        )

def _dynamic_test(test_case, condition, success_message, failure_message):
    if condition:
        test_case._testMethodName = success_message
        test_case.assertTrue(True, success_message)
    else:
        test_case._testMethodName = failure_message
        test_case.fail(failure_message)

def normalize_text(text):
    text = text.lower()
    text = re.sub(r"\s{2,}", " ", text)
    text = re.sub(r"\s*([,:?])\s*", r"\1 ", text)
    return text.strip()

if __name__ == "__main__":
    unittest.main()


test_solution.py

This course is perfect for beginners ready to explore the world of SQL. Whether you're just starting out in database management or aiming to use SQL for your application development projects, this course covers the essentials. You'll quickly learn how to leverage the full potential of SQL, from querying and managing data to seamlessly integrating it into real-world applications. By the end of the course, you'll have the confidence and skills to solve practical problems with SQL and enhance your development process.

Sorting by Multiple Columns

解答