AdventureWorks Database: Retrieve persons last name begins with R

SQL Query - AdventureWorks: Exercise-31 with Solution

31. From the following table write a query in SQL to retrieve those persons whose last name begins with letter 'R'. Return lastname, and firstname and display the result in ascending order on firstname and descending order on lastname columns.

businessentityid|persontype|namestyle|title|firstname               |middlename      |lastname              |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid                             |modifieddate           |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
               1|EM        |false    |     |Ken                     |J               |Sánchez               |      |             0|                     |[XML]       |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
               2|EM        |false    |     |Terri                   |Lee             |Duffy                 |      |             1|                     |[XML]       |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
               3|EM        |false    |     |Roberto                 |                |Tamburello            |      |             0|                     |[XML]       |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
               4|EM        |false    |     |Rob                     |                |Walters               |      |             0|                     |[XML]       |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
               5|EM        |false    |Ms.  |Gail                    |A               |Erickson              |      |             0|                     |[XML]       |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
               6|EM        |false    |Mr.  |Jossef                  |H               |Goldberg              |      |             0|                     |[XML]       |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
               7|EM        |false    |     |Dylan                   |A               |Miller                |      |             2|                     |[XML]       |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
               8|EM        |false    |     |Diane                   |L               |Margheim              |      |             0|                     |[XML]       |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
               9|EM        |false    |     |Gigi                    |N               |Matthew               |      |             0|                     |[XML]       |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns from the Person table
SELECT LastName, FirstName 

-- From the Person schema's Person table
FROM Person.Person  

-- Filtering the results to include only rows where the LastName starts with 'R'
-- LIKE operator is used for pattern matching, 'R%' means any LastName starting with 'R'
WHERE LastName LIKE 'R%'  

-- Ordering the results first by FirstName in ascending order
-- Then by LastName in descending order
ORDER BY FirstName ASC, LastName DESC ;

Explanation:

• The SQL query retrieves data from the Person table within the Person schema.
• It selects two columns: LastName and FirstName.
• The WHERE clause filters the results to include only rows where the LastName starts with the letter 'R'. This is achieved using the LIKE operator with the pattern 'R%'.
• The ORDER BY clause sorts the results first by FirstName in ascending order, and then by LastName in descending order.
• Results will be sorted alphabetically by first name, and within each first name group, they will be sorted in reverse alphabetical order by last name.

Sample Output:

lastname      |firstname  |
--------------+-----------+
Russell       |Aaron      |
Ross          |Aaron      |
Roberts       |Aaron      |
Rana          |Abby       |
Raman         |Abby       |
Russell       |Abigail    |
Ross          |Abigail    |
Ross          |Abigail    |
Rogers        |Abigail    |
Rodriguez     |Abigail    |
Robinson      |Abigail    |
Rivera        |Abigail    |
Richardson    |Abigail    |
Reed          |Abigail    |
Ramirez       |Abigail    |
Russell       |Adam       |
Ross          |Adam       |
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Records of employees, order on hiredate.
Next: Order a table in ASC or DESC condionally.