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

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?
Mike MillerSoftware EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Open in new window

0
nemws1Database AdministratorCommented:
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

0
Mike MillerSoftware EngineerAuthor Commented:
Randy,

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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ray PaseurCommented:
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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
Randy PooleCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
flow01Commented:
SELECT *
FROM (select distinct data from badyear)
ORDER BY REPLACE(REPLACE(data, 'Summer ', ''), 'Fall ', '') DESC
      , REPLACE(REPLACE(data, 'Summer ', '07'), 'Fall ', '12') DESC
0
Mike MillerSoftware EngineerAuthor Commented:
Perfect, thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.