Sorting Hostnames in Domain Order
6.15.1 Problem
You want to sort hostnames in domain order, with the rightmost parts of the names more significant than the leftmost parts.
6.15.2 Solution
Break apart the names and sort the pieces from right to left.
6.15.3 Discussion
Hostnames are strings and therefore their natural sort order is lexical. However, it's often desirable to sort hostnames in domain order, where the rightmost segments of the hostname values are more significant than the leftmost segments. Suppose you have a table hostname that contains the following names:
mysql> SELECT name FROM hostname ORDER BY name; +--------------------+ | name | +--------------------+ | cvs.php.net | | dbi.perl.org | | jakarta.apache.org | | lists.mysql.com | | mysql.com | | www.kitebird.com | +--------------------+
The preceding query demonstrates the natural lexical sort order of the name values. That differs from domain order, as shown by the following table:
|
Lexical order |
Domain order |
|---|---|
|
cvs.php.net |
www.kitebird.com |
|
dbi.perl.org |
mysql.com |
|
jakarta.apache.org |
lists.mysql.com |
|
lists.mysql.com |
cvs.php.net |
|
mysql.com |
jakarta.apache.org |
|
www.kitebird.com |
dbi.perl.org |
Producing domain-ordered output is a substring-sorting problem, where it's necessary to extract each segment of the names so they can be sorted in right-to-left fashion. There is also an additional complication if your values contain different numbers of segments, as our example hostnames do. (Most of them have three segments, but mysql.com has only two.)
To extract the pieces of the hostnames, begin by using SUBSTRING_INDEX( ) in a manner similar to that described previously in Recipe 6.14. The hostname values have a maximum of three segments, from which the pieces can be extracted left to right like this:
SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-3),'.',1) SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-2),'.',1) SUBSTRING_INDEX(name,'.',-1)
These expressions work properly as long as all the hostnames have three components. But if a name has fewer than three, we don't get the correct result, as the following query demonstrates:
mysql> SELECT name, -> SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-3),'.',1) AS leftmost, -> SUBSTRING_INDEX(SUBSTRING_INDEX(name,'.',-2),'.',1) AS middle, -> SUBSTRING_INDEX(name,'.',-1) AS rightmost -> FROM hostname; +--------------------+----------+----------+-----------+ | name | leftmost | middle | rightmost | +--------------------+----------+----------+-----------+ | cvs.php.net | cvs | php | net | | dbi.perl.org | dbi | perl | org | | lists.mysql.com | lists | mysql | com | | mysql.com | mysql | mysql | com | | jakarta.apache.org | jakarta | apache | org | | www.kitebird.com | www | kitebird | com | +--------------------+----------+----------+-----------+
Notice the output for the mysql.com row; it has mysql for the value of the leftmost column, where it should have an empty string. The segment-extraction expressions work by pulling off the rightmost n segments, then returning the leftmost segment of the result. The source of the problem for mysql.com is that if there aren't n segments, the expression simply returns the leftmost segment of however many there are. To fix this problem, prepend a sufficient number of periods to the hostname values to guarantee that they have the requisite number of segments:
mysql> SELECT name,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('..',name),'.',-3),'.',1)
-> AS leftmost,
-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.',name),'.',-2),'.',1)
-> AS middle,
-> SUBSTRING_INDEX(name,'.',-1) AS rightmost
-> FROM hostname;
+--------------------+----------+----------+-----------+
| name | leftmost | middle | rightmost |
+--------------------+----------+----------+-----------+
| cvs.php.net | cvs | php | net |
| dbi.perl.org | dbi | perl | org |
| lists.mysql.com | lists | mysql | com |
| mysql.com | | mysql | com |
| jakarta.apache.org | jakarta | apache | org |
| www.kitebird.com | www | kitebird | com |
+--------------------+----------+----------+-----------+
That's pretty ugly. But these expressions do serve to extract the substrings that are needed for sorting hostname values correctly in right-to-left fashion:
mysql> SELECT name FROM hostname
-> ORDER BY
-> SUBSTRING_INDEX(name,'.',-1),
-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('.',name),'.',-2),'.',1),
-> SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT('..',name),'.',-3),'.',1);
+--------------------+
| name |
+--------------------+
| www.kitebird.com |
| mysql.com |
| lists.mysql.com |
| cvs.php.net |
| jakarta.apache.org |
| dbi.perl.org |
+--------------------+
If you had hostnames with a maximum of four segments rather than three, you'd need to add to the ORDER BY clause another SUBSTRING_INDEX( ) expression that prepends three dots to the hostname values.
Using the mysql Client Program
- Introduction
- Setting Up a MySQL User Account
- Creating a Database and a Sample Table
- Starting and Terminating mysql
- Specifying Connection Parameters by Using Option Files
- Protecting Option Files
- Mixing Command-Line and Option File Parameters
- What to Do if mysql Cannot Be Found
- Setting Environment Variables
- Issuing Queries
- Selecting a Database
- Canceling a Partially Entered Query
- Repeating and Editing Queries
- Using Auto-Completion for Database and Table Names
- Using SQL Variables in Queries
- Telling mysql to Read Queries from a File
- Telling mysql to Read Queries from Other Programs
- Specifying Queries on the Command Line
- Using Copy and Paste as a mysql Input Source
- Preventing Query Output from Scrolling off the Screen
- Sending Query Output to a File or to a Program
- Selecting Tabular or Tab-Delimited Query Output Format
- Specifying Arbitrary Output Column Delimiters
- Producing HTML Output
- Producing XML Output
- Suppressing Column Headings in Query Output
- Numbering Query Output Lines
- Making Long Output Lines More Readable
- Controlling mysqls Verbosity Level
- Logging Interactive mysql Sessions
- Creating mysql Scripts from Previously Executed Queries
- Using mysql as a Calculator
- Using mysql in Shell Scripts
Writing MySQL-Based Programs
- Introduction
- Connecting to the MySQL Server, Selecting a Database, and Disconnecting
- Checking for Errors
- Writing Library Files
- Issuing Queries and Retrieving Results
- Moving Around Within a Result Set
- Using Prepared Statements and Placeholders in Queries
- Including Special Characters and NULL Values in Queries
- Handling NULL Values in Result Sets
- Writing an Object-Oriented MySQL Interface for PHP
- Ways of Obtaining Connection Parameters
- Conclusion and Words of Advice
Record Selection Techniques
- Introduction
- Specifying Which Columns to Display
- Avoiding Output Column Order Problems When Writing Programs
- Giving Names to Output Columns
- Using Column Aliases to Make Programs Easier to Write
- Combining Columns to Construct Composite Values
- Specifying Which Rows to Select
- WHERE Clauses and Column Aliases
- Displaying Comparisons to Find Out How Something Works
- Reversing or Negating Query Conditions
- Removing Duplicate Rows
- Working with NULL Values
- Negating a Condition on a Column That Contains NULL Values
- Writing Comparisons Involving NULL in Programs
- Mapping NULL Values to Other Values for Display
- Sorting a Result Set
- Selecting Records from the Beginning or End of a Result Set
- 18 Pulling a Section from the Middle of a Result Set
- Choosing Appropriate LIMIT Values
- Calculating LIMIT Values from Expressions
- What to Do When LIMIT Requires the Wrong Sort Order
- Selecting a Result Set into an Existing Table
- Creating a Destination Table on the Fly from a Result Set
- Moving Records Between Tables Safely
- Creating Temporary Tables
- Cloning a Table Exactly
- Generating Unique Table Names
Working with Strings
- Introduction
- Writing Strings That Include Quotes or Special Characters
- Preserving Trailing Spaces in String Columns
- Testing String Equality or Relative Ordering
- Decomposing or Combining Strings
- Checking Whether a String Contains a Substring
- Pattern Matching with SQL Patterns
- Pattern Matching with Regular Expressions
- Matching Pattern Metacharacters Literally
- Controlling Case Sensitivity in String Comparisons
- Controlling Case Sensitivity in Pattern Matching
- Using FULLTEXT Searches
- Using a FULLTEXT Search with Short Words
- Requiring or Excluding FULLTEXT Search Words
- Performing Phrase Searches with a FULLTEXT Index
Working with Dates and Times
- Introduction
- Changing MySQLs Date Format
- Telling MySQL How to Display Dates or Times
- Determining the Current Date or Time
- Decomposing Dates and Times Using Formatting Functions
- Decomposing Dates or Times Using Component-Extraction Functions
- Decomposing Dates or Times Using String Functions
- Synthesizing Dates or Times Using Formatting Functions
- Synthesizing Dates or Times Using Component-Extraction Functions
- Combining a Date and a Time into a Date-and-Time Value
- Converting Between Times and Seconds
- Converting Between Dates and Days
- Converting Between Date-and-Time Values and Seconds
- Adding a Temporal Interval to a Time
- Calculating Intervals Between Times
- Breaking Down Time Intervals into Components
- Adding a Temporal Interval to a Date
- Calculating Intervals Between Dates
- Canonizing Not-Quite-ISO Date Strings
- Calculating Ages
- Shifting Dates by a Known Amount
- Finding First and Last Days of Months
- Finding the Length of a Month
- Calculating One Date from Another by Substring Replacement
- Finding the Day of the Week for a Date
- Finding Dates for Days of the Current Week
- Finding Dates for Weekdays of Other Weeks
- Performing Leap Year Calculations
- Treating Dates or Times as Numbers
- Forcing MySQL to Treat Strings as Temporal Values
- Selecting Records Based on Their Temporal Characteristics
- Using TIMESTAMP Values
- Recording a Rows Last Modification Time
- Recording a Rows Creation Time
- Performing Calculations with TIMESTAMP Values
- Displaying TIMESTAMP Values in Readable Form
Sorting Query Results
- Introduction
- Using ORDER BY to Sort Query Results
- Sorting Subsets of a Table
- Sorting Expression Results
- Displaying One Set of Values While Sorting by Another
- Sorting and NULL Values
- Controlling Case Sensitivity of String Sorts
- Date-Based Sorting
- Sorting by Calendar Day
- Sorting by Day of Week
- Sorting by Time of Day
- Sorting Using Substrings of Column Values
- Sorting by Fixed-Length Substrings
- Sorting by Variable-Length Substrings
- Sorting Hostnames in Domain Order
- Sorting Dotted-Quad IP Values in Numeric Order
- Floating Specific Values to the Head or Tail of the Sort Order
- Sorting in User-Defined Orders
- Sorting ENUM Values
Generating Summaries
- Introduction
- Summarizing with COUNT( )
- Summarizing with MIN( ) and MAX( )
- Summarizing with SUM( ) and AVG( )
- Using DISTINCT to Eliminate Duplicates
- Finding Values Associated with Minimum and Maximum Values
- Controlling String Case Sensitivity for MIN( ) and MAX( )
- Dividing a Summary into Subgroups
- Summaries and NULL Values
- Selecting Only Groups with Certain Characteristics
- Determining Whether Values are Unique
- Grouping by Expression Results
- Categorizing Non-Categorical Data
- Controlling Summary Display Order
- Finding Smallest or Largest Summary Values
- Date-Based Summaries
- Working with Per-Group and Overall Summary Values Simultaneously
- Generating a Report That Includes a Summary and a List
Modifying Tables with ALTER TABLE
- Introduction
- Dropping, Adding, or Repositioning a Column
- Changing a Column Definition or Name
- The Effect of ALTER TABLE on Null and Default Value Attributes
- Changing a Columns Default Value
- Changing a Table Type
- Renaming a Table
- Adding or Dropping Indexes
- Eliminating Duplicates by Adding an Index
- Using ALTER TABLE to Normalize a Table
Obtaining and Using Metadata
- Introduction
- Obtaining the Number of Rows Affected by a Query
- Obtaining Result Set Metadata
- Determining Presence or Absence of a Result Set
- Formatting Query Results for Display
- Getting Table Structure Information
- Getting ENUM and SET Column Information
- Database-Independent Methods of Obtaining Table Information
- Applying Table Structure Information
- Listing Tables and Databases
- Testing Whether a Table Exists
- Testing Whether a Database Exists
- Getting Server Metadata
- Writing Applications That Adapt to the MySQL Server Version
- Determining the Current Database
- Determining the Current MySQL User
- Monitoring the MySQL Server
- Determining Which Table Types the Server Supports
Importing and Exporting Data
- Introduction
- Importing Data with LOAD DATA and mysqlimport
- Specifying the Datafile Location
- Specifying the Datafile Format
- Dealing with Quotes and Special Characters
- Importing CSV Files
- Reading Files from Different Operating Systems
- Handling Duplicate Index Values
- Getting LOAD DATA to Cough Up More Information
- Dont Assume LOAD DATA Knows More than It Does
- Skipping Datafile Lines
- Specifying Input Column Order
- Skipping Datafile Columns
- Exporting Query Results from MySQL
- Exporting Tables as Raw Data
- Exporting Table Contents or Definitions in SQL Format
- Copying Tables or Databases to Another Server
- Writing Your Own Export Programs
- Converting Datafiles from One Format to Another
- Extracting and Rearranging Datafile Columns
- Validating and Transforming Data
- Validation by Direct Comparison
- Validation by Pattern Matching
- Using Patterns to Match Broad Content Types
- Using Patterns to Match Numeric Values
- Using Patterns to Match Dates or Times
- Using Patterns to Match Email Addresses and URLs
- Validation Using Table Metadata
- Validation Using a Lookup Table
- Converting Two-Digit Year Values to Four-Digit Form
- Performing Validity Checking on Date or Time Subparts
- Writing Date-Processing Utilities
- Using Dates with Missing Components
- Performing Date Conversion Using SQL
- Using Temporary Tables for Data Transformation
- Dealing with NULL Values
- Guessing Table Structure from a Datafile
- A LOAD DATA Diagnostic Utility
- Exchanging Data Between MySQL and Microsoft Access
- Exchanging Data Between MySQL and Microsoft Excel
- Exchanging Data Between MySQL and FileMaker Pro
- Exporting Query Results as XML
- Importing XML into MySQL
- Epilog
Generating and Using Sequences
- Introduction
- Using AUTO_INCREMENT To Set Up a Sequence Column
- Generating Sequence Values
- Choosing the Type for a Sequence Column
- The Effect of Record Deletions on Sequence Generation
- Retrieving Sequence Values
- Determining Whether to Resequence a Column
- Extending the Range of a Sequence Column
- Renumbering an Existing Sequence
- Reusing Values at the Top of a Sequence
- Ensuring That Rows Are Renumbered in a Particular Order
- Starting a Sequence at a Particular Value
- Sequencing an Unsequenced Table
- Using an AUTO_INCREMENT Column to Create Multiple Sequences
- Managing Multiple SimultaneousAUTO_INCREMENT Values
- Using AUTO_INCREMENT Valuesto Relate Tables
- Using Single-Row Sequence Generators
- Generating Repeating Sequences
- Numbering Query Output Rows Sequentially
Using Multiple Tables
- Introduction
- Combining Rows in One Table with Rows in Another
- Performing a Join Between Tables in Different Databases
- Referring to Join Output Column Names in Programs
- Finding Rows in One Table That Match Rows in Another
- Finding Rows with No Match in Another Table
- Finding Rows Containing Per-Group Minimum or Maximum Values
- Computing Team Standings
- Producing Master-Detail Lists and Summaries
- Using a Join to Fill in Holes in a List
- Enumerating a Many-to-Many Relationship
- Comparing a Table to Itself
- Calculating Differences Between Successive Rows
- Finding Cumulative Sums and Running Averages
- Using a Join to Control Query Output Order
- Converting Subselects to Join Operations
- Selecting Records in Parallel from Multiple Tables
- Inserting Records in One Table That Include Values from Another
- Updating One Table Based on Values in Another
- Using a Join to Create a Lookup Table from Descriptive Labels
- Deleting Related Rows in Multiple Tables
- Identifying and Removing Unattached Records
- Using Different MySQL Servers Simultaneously
Statistical Techniques
- Introduction
- Calculating Descriptive Statistics
- Per-Group Descriptive Statistics
- Generating Frequency Distributions
- Counting Missing Values
- Calculating Linear Regressions or Correlation Coefficients
- Generating Random Numbers
- Randomizing a Set of Rows
- Selecting Random Items from a Set of Rows
- Assigning Ranks
Handling Duplicates
- Introduction
- Preventing Duplicates from Occurring in a Table
- Dealing with Duplicates at Record-Creation Time
- Counting and Identifying Duplicates
- Eliminating Duplicates from a Query Result
- Eliminating Duplicates from a Self-Join Result
- Eliminating Duplicates from a Table
Performing Transactions
- Introduction
- Verifying Transaction Support Requirements
- Performing Transactions Using SQL
- Performing Transactions from Within Programs
- Using Transactions in Perl Programs
- Using Transactions in PHP Programs
- Using Transactions in Python Programs
- Using Transactions in Java Programs
- Using Alternatives to Transactions
Introduction to MySQL on the Web
- Introduction
- Basic Web Page Generation
- Using Apache to Run Web Scripts
- Using Tomcat to Run Web Scripts
- Encoding Special Characters in Web Output
Incorporating Query Resultsinto Web Pages
- Introduction
- Displaying Query Results as Paragraph Text
- Displaying Query Results as Lists
- Displaying Query Results as Tables
- Displaying Query Results as Hyperlinks
- Creating a Navigation Index from Database Content
- Storing Images or Other Binary Data
- Retrieving Images or Other Binary Data
- Serving Banner Ads
- Serving Query Results for Download
Processing Web Input with MySQL
- Introduction
- Creating Forms in Scripts
- Creating Single-Pick Form Elements from Database Content
- Creating Multiple-Pick Form Elements from Database Content
- Loading a Database Record into a Form
- Collecting Web Input
- Validating Web Input
- Using Web Input to Construct Queries
- Processing File Uploads
- Performing Searches and Presenting the Results
- Generating Previous-Page and Next-Page Links
- Generating Click to Sort Table Headings
- Web Page Access Counting
- Web Page Access Logging
- Using MySQL for Apache Logging
Using MySQL-Based Web Session Management
- Introduction
- Using MySQL-Based Sessions in Perl Applications
- Using MySQL-Based Storage with the PHP Session Manager
- Using MySQL for Session BackingStore with Tomcat
Appendix A. Obtaining MySQL Software
- Appendix A. Obtaining MySQL Software
- A.1. Obtaining Sample Source Code and Data
- A.2. Obtaining MySQL and Related Software
Appendix B. JSP and Tomcat Primer
- Appendix B. JSP and Tomcat Primer
- B.1. Servlet and JavaServer Pages Overview
- B.2. Setting Up a Tomcat Server
- B.3. Web Application Structure
- B.4. Elements of JSP Pages
Appendix C. References
