Which clause would you include in a select statement to sort the rows returned by the lastname column?

It can also be used in an INSERT statement or a CREATE VIEW statement.

An ORDER BY clause allows you to specify the order in which rows appear in the result set. In subqueries, the ORDER BY clause is meaningless unless it is accompanied by one or both of the result offset and fetch first clauses or in conjunction with the ROW_NUMBER function, since there is no guarantee that the order is retained in the outer result set. It is permissible to combine ORDER BY on the outer query with ORDER BY in subqueries.

Syntax

ORDER BY { column-Name | ColumnPosition | Expression }
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
    [ , column-Name | ColumnPosition | Expression 
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
    ] * 

Notes

• If SELECT DISTINCT is specified or if the SELECT statement contains a GROUP BY clause, the ORDER BY columns must be in the SELECT list.
• An ORDER BY clause prevents a SELECT statement from being an updatable cursor. For more information, see Requirements for updatable cursors and updatable ResultSets.
• If the null ordering is not specified then the handling of the null values is:
  • NULLS LAST if the sort is ASC
  • NULLS FIRST if the sort is DESC
• If neither ascending nor descending order is specified, and the null ordering is also not specified, then both defaults are used and thus the order will be ascending with NULLS LAST.

Example using a correlation name

You can sort the result set by a correlation name, if the correlation name is specified in the select list. For example, to return from the CITIES database all of the entries in the CITY_NAME and COUNTRY columns, where the COUNTRY column has the correlation name NATION, you specify this SELECT statement:

SELECT CITY_NAME, COUNTRY AS NATION 
    FROM CITIES 
    ORDER BY NATION

Example using a numeric expression

You can sort the result set by a numeric expression, for example:

SELECT name, salary, bonus FROM employee 
   ORDER BY salary+bonus

In this example, the salary and bonus columns are DECIMAL data types.

Example using a function

You can sort the result set by invoking a function, for example:

SELECT i, len FROM measures 
   ORDER BY sin(i) 

Example specifying null ordering

You can specify the position of NULL values using the null ordering specification:

SELECT * FROM t1 ORDER BY c1 DESC NULLS LAST

When you run a SELECT query without any sorting options, the SQL server returns the records in an indiscriminate order. In most cases, the SQL server returns records in the same order they are added to the database. There is no guarantee that records are returned in a specific order when you don't use sorting options in SQL. In addition to sorting, you also use filtering options to return only specific records that match your requirements. 

Sorting Your Records

SQL uses the ORDER BY statement to sort records. You can sort records in ascending or descending order, and you can sort records based on multiple columns. SQL lets you sort alphabetically, numerically or chronologically. 

For instance, suppose you want to get a list of your customers, and you need the list in alphabetical order by state. The following is your current list of customers. 

If you have thousands of customers and you want to see a list of customers in a specific state without excluding any other states, it would be too difficult to browse through your data without any type of sorting ability. You can sort your data by state using the following SQL statement.

SELECT * FROM Customer

ORDER BY State 

In the above statement, your data is returned and alphabetically sorted by state. Data is sorted in ascending order. Ascending order is set by default but you could also add the "ASC" keyword to your statement. The following SQL statement is the same as the above statement. 

SELECT * FROM Customer

ORDER BY State ASC 

Notice the difference is the ASC because it's implied when you eliminate it from your SQL statements. 

Your data is still stored without the sorting, but the SELECT statement shows you the following data set. 

You can also list data in descending order. The DESC or DESCENDING keyword lists data in descending order. Taking the same data set as you used with the ASC order statement, let's reverse the customers. The following code is how you write your DESC SQL statement.

SELECT * FROM Customer

ORDER BY State DESC

SQL lets you order records based on multiple columns. For instance, you might want to sort your records based on state and then last name. The result would give you a list of people grouped by the customer's state and then ordered by their last names. You separate columns by appending a comma and then adding another column parameter in the ORDER BY statement. The following SQL statement is an example.

SELECT * FROM Customer

ORDER BY State DESC, Last_name ASC

The ASC phrase is used in the above statement for clarity. When you read the statement, you know your record set is ordered in descending order and then ordered in ascending order by last name. Your data set turns into the following.

Notice record number 321 and 458 were switched, because the last names were sorted with the state.

Filtering Records and WHERE

We used the WHERE clause several times already to show you how to filter records when you use SELECT, UPDATE, and DELETE statements. You can use the WHERE clause with or without the ORDER BY statement. You can filter records by finite values, comparison values or with sub-SELECT statements. The WHERE clause gives you several options when filtering data. 

We've used several examples with the equal ( = ) sign. You can also use comparisons. For instance, you might want to get a list of customers with IDs between 300 and 400. The following SQL statement displays these values. 

SELECT * FROM Customer

WHERE CustomerId >=200 AND CustomerId <= 300

ORDER BY State 

Notice that the >= and <= phrase are used. The equal sign includes the values you the right of them. In other words, 200 and 300 are included in the search. In this example Customer table, there is no 200 or 300, so those values aren't returned. Notice the syntax also includes an "AND" in the SQL statement. The AND keyword includes a filter from the next SQL statement, in this case it's "Customer <= 300". When you use the AND keyword, you tell the SQL statement to filter records with both parameters. 

The above SELECT statement returns the following data set. 

Our example Customer table only has one record within the given range.

The WHERE clause can use the OR phrase instead of the AND phrase. The following statement replaces AND with OR.

SELECT * FROM Customer

WHERE CustomerId >=200 OR CustomerId <= 300

ORDER BY State

The statement above says "return all customers with an ID greater than 200 or an ID less than 300." The above SELECT statement returns the following results.

Notice that all the records were returned. To understand why all records are returned, you have to turn your WHERE clause into parts. The first part is "CustomerId >=200". All of your records have an ID higher than 200, so the first part of your WHERE clause returns all records.

The next part is OR, which is an important change from the AND statement. The OR statement says to keep the original data set but also return customers with an ID less than 300. You have no customers with an ID less than 300, so the second part returns no records. The difficult part to understand in this SQL statement is why the first statement with AND returns 1 record and the second returns all records. The AND statement says the second part of your WHERE clause must also apply, so your records must answer true for both conditions. The second SQL statement returns records that return true for either the first condition or the second condition. The logic behind the two statements is completely different. Since all of your records answer true for the first condition, the OR statement allows these records to pass through and display in results.

The IN statement has been used in previous chapters, but you can also specify the values you want to return in your IN statement. In previous chapters, a sub-SELECT query was used. You can also use IN to specify values such as the state you want to return. The following SQL statement is an example. 

SELECT * FROM Customer

WHERE State IN (‘tx,' ‘fl') 

The IN phrase makes your SQL code easier to read instead of using an OR statement. The above statement says "get all customers that have a state that equals to TX OR FL. The following table is your results. 

The complexity of the WHERE clause increases as you use more conditions. 

Dates are commonly used in SQL statements. The example table doesn't contain any dates, but imagine the table had a date column named "SignupDate." The SignupDate column indicates when the customer signed up on your website. You can then run reports based on the date the customer signed up on your site. The following code is an example. 

SELECT * FROM Customer

WHERE SignupDate BETWEEN ‘1/1/2014' AND ‘12/31/2014'

ORDER BY State 

The above SQL statement gets records that have a date between the first day of the year in 2014 and the last day of the year. With date values, SQL includes the dates listed in the parameters. The above statement can also be written like the following. 

SELECT * FROM Customer

WHERE SignupDate >= ‘1/1/2014' AND SignupDate <= ‘12/31/2014'

ORDER BY State 

The WHERE clause lets you use a LIKE statement. You use the LIKE operator when you need a list of customers based on part of the values. For instance, suppose you have several customers in Dallas, but you also have customers located in cities that begin with "Da" and need to see them. The LIKE operator does this job for you. The following statement searches all customers that begin with the value "da" and return them. 

SELECT * FROM Customer

WHERE State LIKE ‘da%' 

The percent sign is the wildcard character in this statement. The above statement returns any customer located in Dallas but also customers in other cities that start with Da. The LIKE statement is a great way to return records when you can't remember the exact spelling of a particular value. 

The WHERE and SORT statements are always used at some point in your SQL programming career. Whether you have your own website or code for a customer, these two SQL phrases are useful when learning the language.

Which clause would you include in a SELECT statement to sort the rows returned by the last name?

Which of the following clause is the last clause in the SELECT statement?

Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10?

What would you use in the SELECT clause to return all the columns in the table?