• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

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?
0
Mike Miller
Asked:
Mike Miller
1 Solution
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists β€” all with no agents to manage and no additional licenses to buy.

 
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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered β€œyes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now