Aaron Parecki

#sql

• Run SQL on JSON files -- without any data loads (citusdata.com)

  #database #json #sql

  Sun, Aug 24, 2014 8:59am -07:00
• Window Functions (www.postgresql.org)

  #database #documentation #postgres #sql #tutorial

  Sat, Aug 25, 2012 9:56am -07:00
• (My)SQL mistakes. Do you use GROUP BY correctly? (www.dbasquare.com)

  #db #mysql #sql

  Wed, May 16, 2012 8:09am -07:00
• WhySQL? (blog.evernote.com)

  #evernote #hardware #sql #technology

  Thu, Feb 23, 2012 8:36am -08:00

• Sorting a query in MySQL ignoring the word "The"

  Aaron Parecki

  Sat, Apr 19, 2008 9:50pm -07:00

  When you have a database of books or movies, some of the titles begin with "The." If you do a regular ORDER BY on the table, all the titles that start with "the" get clumped together. One option is running an unsorted query and sorting in PHP, but it would be better to sort at the database level. Here is a query I came up with to do that, using an IF function in MySQL!

  SELECT * FROM movies
  ORDER BY IF(SUBSTRING(title,1,4)="The ",SUBSTRING(title,5),name)
  

  Now create a custom function for that, and it's even easier to use!

  CREATE FUNCTION SORTNAME (name VARCHAR(255)) RETURNS VARCHAR(255)
      RETURN IF( (LCASE(SUBSTRING(name,1,4)) = 'the '), SUBSTRING(name,5), name );
  

  Now you can use it in a query like this:

  SELECT * FROM movies
  ORDER BY SORTNAME(title)
  

  #code #database #MySQL #sorting #SQL

  Sat, Apr 19, 2008 9:50pm -07:00