Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Ordering Query of Query by Date

Posted on 2013-11-15
8
Medium Priority
?
243 Views
Last Modified: 2013-11-18
I'm trying to aggregate 3 RSS feeds and order them by date, like you do. I've collected them with CFFEED and combined them as follows:

<cfquery name="qAllFeeds" dbtype="query">
	SELECT DC_DATE, TITLE, CONTENT, RSSLINK FROM feedquery1
	UNION
	SELECT DC_DATE, TITLE, CONTENT, RSSLINK FROM feedquery2
	UNION
	SELECT DC_DATE, TITLE, CONTENT, RSSLINK FROM feedquery3
	ORDER BY DC_DATE DESC
</cfquery>

Open in new window


The DC_DATE column is formatted like "Wed, 13 Nov 2013 19:55:00 GMT" which, I didn't realize, is treated like a string in QoQ so everything ends up ordered by day name. I did some Googling and found the suggestion to use CAST, so I modified the query to:

<cfquery name="qAllFeeds" dbtype="query">
	SELECT CAST(DC_DATE AS DATE), TITLE, CONTENT, RSSLINK FROM feedquery1
	UNION
	SELECT CAST(DC_DATE AS DATE), TITLE, CONTENT, RSSLINK FROM feedquery2
	UNION
	SELECT CAST(DC_DATE AS DATE), TITLE, CONTENT, RSSLINK FROM feedquery3
	ORDER BY DC_DATE DESC
</cfquery>

Open in new window


Which results in a very unspecific error:

screenshot of error
How can I get my feed ordered correctly? Thanks!
0
Comment
Question by:SiobhanElara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 44

Expert Comment

by:Chris Stanyon
ID: 39651734
If your date is always in that format, then you can use the STR_TO_DATE function

SELECT
   DATE(STR_TO_DATE(DC_DATE, '%a, %d %b %Y %T')) as newDate,
   TITLE,
   CONTENT,
   RSSLINK
FROM feedquery1

Open in new window

You would then need to ORDER BY on newDate
0
 
LVL 44

Expert Comment

by:Chris Stanyon
ID: 39651757
Just as an addition, the %d in the STR_TO_TIME function assumes your Day number will be 0 padded (01, 02, 03 etc). If it's not (1, 2, 3 etc) then change it to %e
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39651772
@ChrisStanyon - Yes that would probably work w/MySQL but they're using a special in-memory query specific to CF, which doesn't support those functions.

@SiobhanElara - What's the stack trace of the error? If you don't see one, wrap the query in a cftry/cfcatch and dump the error <cfdump var="#cfcatch#">

Also, what are the URL's of one the feeds so we can test them?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 44

Expert Comment

by:Chris Stanyon
ID: 39651781
Ahhh- Didn't realise that :(
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39651802
The tags (ie MySQL) threw me off at first too :) I only noticed it was a QoQ when I looked closer at the code.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 39651851
Hm... I just noticed you're missing an alias when you CAST the columns.  Adding one seemed to work for me w/CF10:

<cfset theURL = "http://feeds.adobe.com/xml/rss.cfm?query=byCategory&languages=1&categoryId=1">
<cffeed action="read" source="#theURL#" query="feedquery1">

<cfset theURL = "http://feeds.adobe.com/xml/rss.cfm?query=byCategory&languages=1&categoryId=6">
<cffeed action="read" source="#theURL#" query="feedquery2">

<cfquery name="qAllFeeds" dbtype="query">
	SELECT CAST(DC_DATE AS DATE) AS DC_DATE, TITLE, CONTENT, RSSLINK FROM feedquery1
	UNION
	SELECT CAST(DC_DATE AS DATE), TITLE, CONTENT, RSSLINK FROM feedquery2
	ORDER BY DC_DATE DESC
</cfquery>

Open in new window

0
 

Author Closing Comment

by:SiobhanElara
ID: 39656279
Perfect, that was exactly the problem! I didn't realize an alias was required there. (For the record, it works in CF8 too.)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39657952
Yeah, probably a quirk of QoQ's. I think it'd work w/o an alias in db query. Though ... you'd still need to add an alias anyway. Otherwise you wouldn't be able to access the "DC_DATE" column in CF ;-)
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

604 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