Query of Queries

[djc11]

Is there any way to simulate this mySQL query in a CF Q of Q? Basically Ineed the results ordered by year, then the word month. I would be willing to perform a simple operation on the Resultset after it has been fetched/created to order it properly also. Either way would work for me.

SELECT bsa_year, bsa_month FROM
(SELECT * FROM data1 WHERE company_id = 'xxx') t1
UNION SELECT isa_year, isa_month FROM
(SELECT * FROM data2 WHERE company_id = 'xxx') t2
ORDER BY bsa_year, FIELD(bsa_month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

[cf_dev2]

If bsa_month is numeric, it should sort that way anyway ie 1,2,3,4,5,6..12

[Dan_Bracuk]

Your query is too complicated. The subqueries accomplish nothing that you could not have achieved by selecting directly from the tables.

[djc11]

I know it could be stored as numbers instead of the letters, but the table was not my work and that's the way it's set up. I could change everything related to it and it probably wouldn't be too much. I just thought if there was a way to sort like that, i wouldn't have to. And i could use in other places that weren't just months.

Dan, I realize i could accomplish this with a simple query, but i already have those queries in memory at the point of this request. The query i pasted in here was what i was testing in sqlYog. In the code the subqueries are replaced by the names of the previous queries. I thought this would be a better method for lowering traffic and be faster since it was in mem already. I realize this is a small example of both of those since it is such a small query, but would that typically be the right idea? Or should i just stick to writing the queries as i need them from the sql server?

-Thank you

[cf_dev2]

Yes, it probably would be simpler to store the value as a number, or the year/month/day as a datetime field.

Afaik, QoQ don't provide the type of function you're looking for. Its a convenience that supports a limited number of functions. Nothing near as extensive as your db's functions.

Another option is to perform the logic in your sql queries and add a "bsa_month_number" column to the query results. Then use that column in your QoQ.

[Dan_Bracuk]

I don't use mySQL so I am not sure what it can and can't do. Since bsa_month is a string, use either a case construct to convert those strings to numbers, or a function on a date field to get the month number. Use that for your 2nd sort field.

Subqueries do not work in Q of Q. You have to select from previous queries.

[cf_dev2]

quote:

---

Originally posted by: Dan Bracuk
I don't use mySQL so I am not sure what it can and can't do. Since bsa_month is a string, use either a case construct to convert those strings to numbers, or a function on a date field to get the month number. Use that for your 2nd sort field.

---

Yes. A CASE statement is one option. Another is to concatenate the year, month and '01' (ie first of month) and use the STR_TO_DATE function to convert the string to a date. Then use that column in your QoQ order by

-- example
STR_TO_DATE('2007-Jan-01', '%Y-%b-%d') AS bsa_date