CODEWITHSUNDEEP
sqlsortingsubstring
Brds
Brds

Reputation: 1075

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

I have a database whose columns are npID, title, URL, and issue.

Here is an example of two years' entries:

npID               title               URL               issue
88                 EMR Newsletter      a.com             2010 Third_Quarter
89                 EMR Newsletter      b.com             2010 Second_Quarter
43                 EMR Newsletter      c.com             2010 First_Quarter
47                 EMR Newsletter      d.com             2009 Winter
45                 EMR Newsletter      e.com             2009 Summer
46                 EMR Newsletter      f.com             2009 Spring
44                 EMR Newsletter      g.com             2009 Fall

What I would like to do is be able to sort the results based on substrings within the "issue" column. However, until 2010, the client used seasons as the header and in 2010, they started using quarters. Is there a way in the "ORDER BY" I can provide a list of words to sort by if/when they're found anywhere in the "issue" value?

I would like the end result to be something like this:

npID               title               URL               issue
43                 EMR Newsletter      c.com             2010 First_Quarter
89                 EMR Newsletter      b.com             2010 Second_Quarter
88                 EMR Newsletter      a.com             2010 Third_Quarter
47                 EMR Newsletter      d.com             2009 Winter
45                 EMR Newsletter      e.com             2009 Summer
46                 EMR Newsletter      f.com             2009 Spring
44                 EMR Newsletter      g.com             2009 Fall

Upvotes: 8

Views: 62714

Answers (7)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

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

Upvotes: 10

Sree
Sree

Reputation: 1

SELECT Column1, row_number() over(order by substring(column2,280,9)) 
FROM YourTable

This will give the order by of substring column2.

Upvotes: 0

Tom H
Tom H

Reputation: 47454

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.

Upvotes: 12

CLo
CLo

Reputation: 3730

You could make the sub string part of the data being selected

SELECT npId, title, URL, issue, SUBSTRING(issue, 4) AS strsort FROM tbl ORDER BY strsort, issue

Upvotes: 0

sorpigal
sorpigal

Reputation: 26086

Try this approach (this is in T-SQL):

select
        *
    from
        your_table as t
    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 'Third_Quarter' then 3
            when 'Fourth_quarter' then 4
            when 'Spring' then 3
            when 'Summer' then 2
            when 'Fall' then 4
            when 'Winter' then 1
            else 5 -- show unexpected input last
        end asc

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 1

Taryn East
Taryn East

Reputation: 27747

Yes, you can use if/then constructions anywhere in a sql command.

Do you already have a substring-pattern that works for you? if so, you can use something like:

ORDER BY (IF (SUBSTRING(first_match_of_issue) IS NOT NULL) THEN first_match_of_issue ELSE second_match_of_issue))

However - if you already know what they're going to be, then the CASE statements mentioned in other answers are probably easier.

Upvotes: 0

Related Questions