Re: Difficult Sort - Theory
MySQL doens't have view support until 5.0, and that's only been out for a few weeks, Once I saw the data I had to work with, and the 3857 different ways the client wanted to search/sort, I was aching to be able to use views! Certainly would have simplified the process! -- --- Les Mizzell ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223666 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
I forgot this is MySQL. My bad... On 11/9/05, Les Mizzell [EMAIL PROTECTED] wrote: MySQL doens't have view support until 5.0, and that's only been out for a few weeks, Once I saw the data I had to work with, and the 3857 different ways the client wanted to search/sort, I was aching to be able to use views! Certainly would have simplified the process! -- --- Les Mizzell ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223733 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
Matthew Walker wrote: Won't this work? SELECTshowId FROM airdates GROUP BY showId ORDER BY AVG(rating) Hmmm - that's an idea, but doesn't seem to want to work. Here's the actual query below. (Vastly simplified) SELECT Airdates.rating, Airdates.ShowID, Shows.Title, Shows.ShowID FROM Shows INNER JOIN Airdates ON (Shows.ShowID = Airdates.ShowID) WHERE Shows.title like '%#form.show_title#%' and Airdates.rating #form.AMOUNT# GROUP by Shows.ShowID ORDER BY Airdates.#form.typeRATE# DESC It works fine until I try until I try to change the order clause: ORDER BY AVG(Airdates.rating) DESC Error: Invalid use of group function mySQL Database Other Ideas? -- --- Les Mizzell ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223616 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Difficult Sort - Theory
Not familiar with MySQL so this may or may not be relevant. Generally, everything you are selecting needs to appear in your group by clause unless it is some kind of aggregation (note you can't have rating in your select clause as you are averaging it). So it may help to remove everything from your select except the average rating and the id (you can put it back later). Also, if your rating is an integer, you may need to convert it to a floating point number to get an accurate average (otherwise you may get an integer back). Here's how it would look in SQL Server... SELECT AVG(CAST(rating AS float)) AS avgRating, ShowID FROM Airdates GROUP BY ShowID ORDER BY AVG(CAST(rating AS float)) DESC I'd be surprised if it was a lot different in MySQL although CAST is probably different. -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Wednesday, 9 November 2005 12:31 p.m. To: CF-Talk Subject: Re: Difficult Sort - Theory Matthew Walker wrote: Won't this work? SELECTshowId FROM airdates GROUP BY showId ORDER BY AVG(rating) Hmmm - that's an idea, but doesn't seem to want to work. Here's the actual query below. (Vastly simplified) SELECT Airdates.rating, Airdates.ShowID, Shows.Title, Shows.ShowID FROM Shows INNER JOIN Airdates ON (Shows.ShowID = Airdates.ShowID) WHERE Shows.title like '%#form.show_title#%' and Airdates.rating #form.AMOUNT# GROUP by Shows.ShowID ORDER BY Airdates.#form.typeRATE# DESC It works fine until I try until I try to change the order clause: ORDER BY AVG(Airdates.rating) DESC Error: Invalid use of group function mySQL Database Other Ideas? -- --- Les Mizzell ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223635 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
This is untested, but should work: select * from ( select shows.showId, shows.showTitle, ( select avg(rating) from airdates where showId = shows.showId ) as avgRating ) t order by avgRating cheers, barneyb On 11/7/05, Les Mizzell [EMAIL PROTECTED] wrote: Just looking for ideas... Assume I have a database with TV Miniseries It's a mySQL database. TABLES - 1. SHOWS a. showID b. showTITLE 2. AIRDATES a. showID b. airdate c. rating Now, since it's a miniseries, it will have multiple entries in the AIRDATES table for each night (part 1, 2, 3, blah, blah..). I need to AVERAGE the rating for all airdates for each specific title, and then sort on the average. I've come up with a couple of VERY convoluted ideas, but the operative word here is convoluted. How would any of you approach this? -- --- Les Mizzell -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223642 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
Thinking about that some more, the outer select should be unneeded. Just move the ORDER BY to the first subquery and you should be good to go. And add the missing FROM shows clause that I completely forgot. select showId, showTitle ( select avg(rating) from airdates where showId = shows.showId ) as avgRating from shows order by avgRating which, aside from formatting differences, is exactly what John proposed yesterday. Note to self: never try and think code while doing drywall. ;) cheers, barneyb On 11/8/05, Barney Boisvert [EMAIL PROTECTED] wrote: This is untested, but should work: select * from ( select shows.showId, shows.showTitle, ( select avg(rating) from airdates where showId = shows.showId ) as avgRating ) t order by avgRating cheers, barneyb -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223645 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
I'll work with this tomorrow and let you know what happens. In the real query, there's another 4 tables that data has to be filtered by and selected from! It gets pretty wild looking.. Thanks for taking time to look at this... Thinking about that some more, the outer select should be unneeded. Just move the ORDER BY to the first subquery and you should be good to go. And add the missing FROM shows clause that I completely forgot. select showId, showTitle ( select avg(rating) from airdates where showId = shows.showId ) as avgRating from shows order by avgRating which, aside from formatting differences, is exactly what John proposed yesterday. Note to self: never try and think code while doing drywall. ;) cheers, barneyb On 11/8/05, Barney Boisvert [EMAIL PROTECTED] wrote: This is untested, but should work: select * from ( select shows.showId, shows.showTitle, ( select avg(rating) from airdates where showId = shows.showId ) as avgRating ) t order by avgRating cheers, barneyb -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223647 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
If you have that much data why not create a view for each one then a view to pull them together? That cut down on confusion. On 11/8/05, Les Mizzell [EMAIL PROTECTED] wrote: I'll work with this tomorrow and let you know what happens. In the real query, there's another 4 tables that data has to be filtered by and selected from! It gets pretty wild looking.. Thanks for taking time to look at this... Thinking about that some more, the outer select should be unneeded. Just move the ORDER BY to the first subquery and you should be good to go. And add the missing FROM shows clause that I completely forgot. select showId, showTitle ( select avg(rating) from airdates where showId = shows.showId ) as avgRating from shows order by avgRating which, aside from formatting differences, is exactly what John proposed yesterday. Note to self: never try and think code while doing drywall. ;) cheers, barneyb On 11/8/05, Barney Boisvert [EMAIL PROTECTED] wrote: This is untested, but should work: select * from ( select shows.showId, shows.showTitle, ( select avg(rating) from airdates where showId = shows.showId ) as avgRating ) t order by avgRating cheers, barneyb -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223648 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
Sorry...I hate mistakes: That could cut down on confusion. On 11/8/05, John C. Bland II [EMAIL PROTECTED] wrote: If you have that much data why not create a view for each one then a view to pull them together? That cut down on confusion. On 11/8/05, Les Mizzell [EMAIL PROTECTED] wrote: I'll work with this tomorrow and let you know what happens. In the real query, there's another 4 tables that data has to be filtered by and selected from! It gets pretty wild looking.. Thanks for taking time to look at this... Thinking about that some more, the outer select should be unneeded. Just move the ORDER BY to the first subquery and you should be good to go. And add the missing FROM shows clause that I completely forgot. select showId, showTitle ( select avg(rating) from airdates where showId = shows.showId ) as avgRating from shows order by avgRating which, aside from formatting differences, is exactly what John proposed yesterday. Note to self: never try and think code while doing drywall. ;) cheers, barneyb On 11/8/05, Barney Boisvert [EMAIL PROTECTED] wrote: This is untested, but should work: select * from ( select shows.showId, shows.showTitle, ( select avg(rating) from airdates where showId = shows.showId ) as avgRating ) t order by avgRating cheers, barneyb -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223649 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
I find subqueries really help this sort of thing, because they let you break a complex problem down into smaller pieces that can be solved (and tested!) individually. One of the best work days in the past few years was when we upgrade to MySQL 4.1 and got subquery support. Probably second only to the upgrade from CF4.5 to 6.1 (for obvious reasons). Once you have a solution, then you can worry about making it more performant. Maybe compact a subquery into a join, maybe do the reverse. Stuff like that. Of course, only wasting time on that stuff if it's warranted by profiling. cheers, barneyb On 11/8/05, Les Mizzell [EMAIL PROTECTED] wrote: I'll work with this tomorrow and let you know what happens. In the real query, there's another 4 tables that data has to be filtered by and selected from! It gets pretty wild looking.. Thanks for taking time to look at this... -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223651 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
MySQL doens't have view support until 5.0, and that's only been out for a few weeks, so I'm going to wager he's probably not running it. Lot of places still run 3.23 for some unknown reason. Using subqueries in the FROM clause (which MySQL 4.1 supports) gets you down that track to some extent, though without the optimizations and reuse of a real view. cheers, barneyb On 11/8/05, John C. Bland II [EMAIL PROTECTED] wrote: If you have that much data why not create a view for each one then a view to pull them together? That cut down on confusion. -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223652 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Difficult Sort - Theory
Just looking for ideas... Assume I have a database with TV Miniseries It's a mySQL database. TABLES - 1. SHOWS a. showID b. showTITLE 2. AIRDATES a. showID b. airdate c. rating Now, since it's a miniseries, it will have multiple entries in the AIRDATES table for each night (part 1, 2, 3, blah, blah..). I need to AVERAGE the rating for all airdates for each specific title, and then sort on the average. I've come up with a couple of VERY convoluted ideas, but the operative word here is convoluted. How would any of you approach this? -- --- Les Mizzell ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223551 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Difficult Sort - Theory
Have you tried an inline statement? SELECT s.*, (select avg(a.rating) from airdates a where a.showID = s.showID) AS 'average' FROM shows s ORDER BY average ASC I'm not 100% sure on the order by since its an alias but you can try it. This also depends on what version of mysql you're using. Disclaimer: Code written on the fly...not tested. On 11/7/05, Les Mizzell [EMAIL PROTECTED] wrote: Just looking for ideas... Assume I have a database with TV Miniseries It's a mySQL database. TABLES - 1. SHOWS a. showID b. showTITLE 2. AIRDATES a. showID b. airdate c. rating Now, since it's a miniseries, it will have multiple entries in the AIRDATES table for each night (part 1, 2, 3, blah, blah..). I need to AVERAGE the rating for all airdates for each specific title, and then sort on the average. I've come up with a couple of VERY convoluted ideas, but the operative word here is convoluted. How would any of you approach this? -- --- Les Mizzell ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223552 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Difficult Sort - Theory
Won't this work? SELECT showId FROMairdates GROUP BYshowId ORDER BYAVG(rating) -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Tuesday, 8 November 2005 4:16 p.m. To: CF-Talk Subject: Difficult Sort - Theory Just looking for ideas... Assume I have a database with TV Miniseries It's a mySQL database. TABLES - 1. SHOWS a. showID b. showTITLE 2. AIRDATES a. showID b. airdate c. rating Now, since it's a miniseries, it will have multiple entries in the AIRDATES table for each night (part 1, 2, 3, blah, blah..). I need to AVERAGE the rating for all airdates for each specific title, and then sort on the average. I've come up with a couple of VERY convoluted ideas, but the operative word here is convoluted. How would any of you approach this? -- --- Les Mizzell ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223553 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
