Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

If you execute this query, you’ll get the results we saw above, listed in reverse

order. You could also order the results by multiple columns—simply add a comma

after the field name and enter a second field name, as follows:

SELECT EmployeeID, Name, City

FROM Employees

ORDER BY City, Name

In this case, the results are returned in alphabetical order by city, and any tying

records (i.e. with the same city) will appear sorted by name.

Limiting the Number of Results with TOP

Another using SQL keyword is TOP, which can be used together with SELECT to

limit the number of returned rows. For example, if we want to retrieve the first

five departments, and have the list ordered alphabetically, we’d use this command:

SELECT TOP 5 Department

FROM Departments

ORDER BY Department

Here are the results:

Department

--------------------------------------------------

Accounting

Administration

Business Development

Customer Support

Engineering

(5 row(s) affected)

Reading Data from Multiple Tables

Until now, we’ve primarily focused on extracting data from a single table. Yet in

many real-world applications, you’ll need to extract data from multiple tables

simultaneously. To do so, you’ll need to use subqueries or joins.

Let’s learn about joins and subqueries by looking closely at a typical example.

Say you’re asked to build a report that displays all the employees in the Engin-

eering department. To find employee data, you’d normally query the Employees

table, and apply a WHERE filter on the ID of the department. That approach would

307

Limiting the Number of Results with TOP