Database Articles

  Home arrow Database Articles arrow Page 4 - Clauses, Sorting, and SQL Queries
DATABASE ARTICLES

Clauses, Sorting, and SQL Queries
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 5
    2007-11-08

    Table of Contents:
  • Clauses, Sorting, and SQL Queries
  • The order by Clause
  • Ascending Versus Descending Sort Order
  • Sorting via Expressions
  • Sorting via Numeric Placeholders

  •  
     

    SEARCH CODEWALKERS

    TOOLS YOU CAN USE

    advertisement

    Clauses, Sorting, and SQL Queries - Sorting via Expressions


    (Page 4 of 5 )

    Sorting your results using column data is all well and good, but sometimes you might need to sort by something that is not stored in the database, and possibly doesn’t appear anywhere in your query. You can add an expression to your order by clause to handle such situations. For example, perhaps you would like to sort your customer data by the last three digits of the customer’s Federal ID number (which is either a Social Security number for individuals or a corporate ID for businesses):

        mysql> SELECT cust_id, cust_type_cd, city, state, fed_id
            -> FROM customer
            -> ORDER BY RIGHT(fed_id, 3);

     

     

    cust_id  cust_type_cd city

     state  fed_id

     

     

     

    1  I

     Lynnfield

     MA

    111-11-1111 

     

    10  B

     Salem

     NH

     04-1111111

     

     

    2  I

    Woburn

     MA

     222-22-2222

     

    11  B

     Wilmington  MA

    04-2222222

     

     

    3  I

     Quincy

    MA

    333-33-3333 

     

    12 | B

     Salem

    NH

     04-3333333

     

     

    13 B

     Quincy

     MA

     04-4444444

     

     

    4  I

    Waltham

     MA

     444-44-4444 

     

    5  I

    Salem

    NH

     555-55-5555

     

    6  I

     Waltham

     MA

     666-66-6666

     

    7  I

     Wilmington MA

     777-77-7777

     

    8  I

     Salem

     NH

     888-88-8888

     

    9  I

     Newton

     MA

    999-99-9999

     

    13 rows in set (0.01 sec)

    This query uses the built-in functionright()to extract the last three characters of thefed_idcolumn and then sorts the rows based on this value.

    More Database Articles Articles
    More By O'Reilly Media

    blog comments powered by Disqus

    DATABASE ARTICLES ARTICLES

    - Completing a Book Inventory Management System
    - Uploading Images for a Book Inventory Manage...
    - Finishing the Add Book Story for a Book Inve...
    - Integration Testing for a Book Inventory Man...
    - User Stories for a Book Inventory Management...
    - Unit Testing a Book Inventory Management Sys...
    - Testing a Book Inventory Management System
    - Implementing Models for a Book Inventory Man...
    - Book Inventory Application: Publishers and B...
    - Handling Publishers in a Book Inventory Mana...
    - Publisher Administration for Book Inventory ...
    - Book Inventory Management
    - Using the SQL Reference Manual
    - Using Oracle SQL Developer with SQL Statemen...
    - Fixing Errors with Oracle SQL Developer


    © 2003-2012 by Developer Shed. All rights reserved. DS Cluster 2 - Follow our Sitemap