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 on which dates specific SDSS spectroscopic plates were observed. One easy way to answer this question would be to retrieve those plates ordered by the Modified Julian date (MJD) on which they were observed, in order from the first date to the last date. SQL's ORDER BY command will sort records in ascending order according to a specified column.

The "plateX" table contains data on SDSS plates (spectroscopic observations of one area of sky). Plates are organized by two numbers: the plate number (plate) and the observation date (mjd). Sometimes multiple plates are observed in one night, and sometimes a single plate is spread out over several nights, so both numbers are necessary.

To look at a few plates - in this case, just the ones numbered 275 or less - in order of observation date - use the ORDER BY command. The syntax of ORDER BY is simple: just type "order by" followed by the name of the data column by which 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 plates by observation date, then, use the query below:

select mjd,plate
from plateX
where plate <= 275
order by mjd
            

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!

Modify the query in the textbox below to order by plate number instead of MJD. Which of the plates was observed over more than one night?

Show Sample Solution

select mjd,plate
from plateX
where plate <= 275
order by plate

See the Plate Browser for a list of the plates available in Data Release 16.