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:

select
    run, camcol, field, nobjects
from
    field
where
    run=756
order by 
    nobjects desc
            

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.

Try It!

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?    Answer

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