Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Ordering Query of Query by Date

Posted on 2013-11-15
8
Medium Priority
?
244 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
  • 4
  • 3
8 Comments
 
LVL 45

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 45

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 53

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 45

Expert Comment

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

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 53

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 53

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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
Course of the Month14 days, 23 hours left to enroll

578 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