Tutorials
Help
 Archive Intro
 Table Descriptions
 Schema Browser
 Glossary
 Algorithms
 Introduction to SQL
 Form Query User Guide
 Query Limits
 How To
     - Search
     - Graph
 FAQ
 API
 sdssQA
 Download
 SkyServer Sites
 SkyServer Traffic Page
 Web Browsers
 Contact Help Desk
Searching
 Back
 1. Introduction
 2. A Simple Query
 Practice
 3. Common Searches
 4. More Samples
 Practice
 5. Multiple Tables
 Practice
 6. Aggregate Fcns.
 7. Group By
 8. Order By
 Practice
 9. Views
 10. Functions
 Practice
 11. Conclusion

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.

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

select
specObj.bestObjID, count(specLine.restwave) as numlines

from
specObj, specLine

where
specObj.specObjID = specLine.specObjID AND
specLine.restwave > 0 AND
specObj.bestObjID <> 0

group by specObj.bestObjID

order by count(specline.restwave) desc

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

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.


Format HTML XML CSV



Enter your SQL query in the text box. The query is limited to 90 seconds and 1,000 rows.