SQL statement help: SELECT with Order by Date(Year) with order by text entries

Posted on 2014-08-03
Last Modified: 2014-08-03
I have a table called tblPlayers with a column called ScheduleYear.

In the column ScheduleYear, there are entries like:

Fall 2014
Summer 2014
Fall 2013
Summer 2013
Fall 2012
Summer 2012
Fall 2011
Summer 2011

I want to create a SELECT DISTINCT query that displays them in that order (Descending by Year with Fall first, then Summer). The data never deviates from that convention. It is always a year preceded by the word "Fall" or the word "Summer."

How would I do this?
Question by:mwmiller78
    LVL 21

    Expert Comment

    by:Randy Poole
    Select * from tblPlayers order by convert(int,SUBSTRING(ScheduleYear,CHARINDEX(' ',ScheduleYear),len(ScheduleYear))) desc,LEFT(ScheduleYear,CHARINDEX(' ',ScheduleYear))

    Open in new window

    LVL 23

    Expert Comment

    You'll have to call a couple functions on it to sort it that way.  I can get it to work with just using REPLACE() and 2 parameters to my ORDER BY.  The first one orders by the year (just removes the Summer/Fall strings).  The second orders by summer (July/07) or Fall (December/12).

    CREATE TABLE badyear (data VARCHAR(40));
    INSERT INTO badyear VALUES ('Fall 2011');
    INSERT INTO badyear VALUES ('Fall 2012');
    INSERT INTO badyear VALUES ('Fall 2013');
    INSERT INTO badyear VALUES ('Fall 2014');
    INSERT INTO badyear VALUES ('Summer 2011');
    INSERT INTO badyear VALUES ('Summer 2012');
    INSERT INTO badyear VALUES ('Summer 2013');
    INSERT INTO badyear VALUES ('Summer 2014');
    SELECT *
    FROM badyear
    ORDER BY REPLACE(REPLACE(data, 'Summer ', ''), 'Fall ', '') DESC
    	, REPLACE(REPLACE(data, 'Summer ', '07'), 'Fall ', '12') DESC

    Open in new window


    Author Comment


    Your example orders great however when I run SELECT DISTINCT, I get:

    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
    What can I change?
    LVL 107

    Expert Comment

    by:Ray Paseur
    Going forward you might want to use the ISO-8601 standard for date and time values.  It makes this sort of query very, very simple.  This article explains how to use DATETIME values in PHP and MySQL.
    LVL 21

    Accepted Solution

    Just add these into your select
    Select Distinct *,convert(int,SUBSTRING(ScheduleYear,CHARINDEX(' ',ScheduleYear),len(ScheduleYear))) ,LEFT(ScheduleYear,CHARINDEX(' ',ScheduleYear)) from

    Open in new window

    LVL 20

    Expert Comment

    SELECT *
    FROM (select distinct data from badyear)
    ORDER BY REPLACE(REPLACE(data, 'Summer ', ''), 'Fall ', '') DESC
          , REPLACE(REPLACE(data, 'Summer ', '07'), 'Fall ', '12') DESC

    Author Closing Comment

    Perfect, thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now