SQL: ORDER BY using a substring within a specific column... possible?

Solution 1:

You can put a CASE statement in the ORDER BY to accomplish this. A better route would be to change the application and table to actually store this relevant data in columns where it belongs when you have the development time to do that.

ORDER BY
    CAST(SUBSTRING(issue, 1, 4) AS INT) DESC,  -- Year
    CASE
        WHEN issue LIKE '%First_Quarter' OR issue LIKE '%Winter' THEN 1
        WHEN issue LIKE '%Second_Quarter' OR issue LIKE '%Spring' THEN 2
        WHEN issue LIKE '%Third_Quarter' OR issue LIKE '%Summer' THEN 3
        WHEN issue LIKE '%Fourth_Quarter' OR issue LIKE '%Fall' THEN 4
    END

Order the seasons however you want. You could also order them in a specific way (Q1 followed by Spring, followed by Q2, etc.) by adjusting the CASE statement.

Solution 2:

Standard SQL

Try a CASE statement in the ORDER BY:

SELECT npID, title, URL, issue
FROM   tbl
ORDER  BY substring(issue, 1, 4) DESC
     , CASE 
          WHEN substring(issue, 6, 100) IN ('Winter','First_Quarter')  THEN 1
          WHEN substring(issue, 6, 100) IN ('Summer','Second_Quarter') THEN 2
          WHEN substring(issue, 6, 100) IN ('Spring','Third_Quarter')  THEN 3
          WHEN substring(issue, 6, 100) IN ('Fall',  'Fourth Quarter') THEN 4
          ELSE 5 
       END;

Winter -> Summer -> Spring - it's what the client wants! :)

Optimize performance

A "simple" CASE should perform better since the expression is only evaluated once.
And right(issue, -5) is equivalent to substring(issue, 6, 100), but a bit faster:

SELECT npid, title, url, issue
FROM   tbl
ORDER  BY left(issue, 4) DESC
     , CASE right(issue, -5)
          WHEN 'Winter'         THEN 1
          WHEN 'First_Quarter'  THEN 1
          WHEN 'Summer'         THEN 2
          WHEN 'Second_Quarter' THEN 2
          WHEN 'Spring'         THEN 3
          WHEN 'Third_Quarter'  THEN 3
          WHEN 'Fall'           THEN 4
          WHEN 'Fourth Quarter' THEN 4
          ELSE 5 
       END;

left() and right() have been added with PostgreSQL 9.1. The trick with right() is to use a negative number to trim a constant number of characters from the left.

Syntax variants

This is equivalent (for strings of <= 100 characters):

SELECT substring(issue from 6 for 100) AS substring1
     , substring(issue, 6, 100)        AS substring2
     , substring(issue, 6)             AS substring3
     , substr(issue, 6, 100)           AS substr1
     , substr(issue, 6)                AS substr2
     , right(issue, -5)                AS right0
FROM   tbl;

-> sqlfiddle

Solution 3:

Here's a variation of the same theme

ORDER BY
    SUBSTRING(issue,1,4) Desc,

CASE SUBSTRING(issue,6, LEN(issue) - 5)
   WHEN 'First_Quarter' THEN 1
   WHEN 'Second_Quarter' THEN 2
   WHEN 'Second_Quarter' THEN 3
   WHEN 'Winter' then 1
   WHEN 'Spring' then 2
   WHEN 'Summer' then 3
   WHEN 'Fall' then 4
END