The Order By Command

Sometimes, you might want to ask questions containing the words "the most" and "the least." For example, you might want to know which SDSS field contains the most objects. One easy way to answer this question would be to retrieve all the fields ordered by number of objects, with the most objects at the top. SQL's order by command will sort records in ascending order according to a specified column.

The "field" table contains data on SDSS fields (observations of one area of sky). Fields are organized by three numbers: run, camcol, and field. The parameter "nobjects" shows the number of objects contained in each field. The order by command is simple: just type "order by" followed by the column you want to sort. If you want the data sorted in descending order, from most to least, add "desc" to the end of the command. To sort fields by number of objects, then, use the query below:

The order by command also works with the group by command. Use them both together when you want to sort groups of records by a trait you chose to return. Put the group by command after the where block, followed by the order by command. Make sure that the column you list in the order by command is also in the group by command OR contains an aggregate function.

For example, the query below searches the specObj and specLine tables to find which objects have the most lines identified in their spectra:

Try It!

Try pressing the buttons "Query 1" and "Query 2" below. These buttons will make the two queries above (in the purple boxes) appear in the query window. Press Submit to execute the queries.

Try running Query 1. Record which field has the largest number of objects - write its run, camcol, and field. Then, use the Get Fields tool to look at that field. What do you see in the field? How can that account for the unusually large number of objects?

Try modifying the query slightly to find fields with no detected objects.

Try running Query 2. Which objects have the most spectral lines identified? If you like, use the Object Explorer to examine the objects. What are these objects classified as? What about objects with few identified spectral lines?

When you are ready to move on, click Next to get some more practice writing queries with aggregate functions, group by statements, and order by statements.