Re: Group - but with no order specified
> Also the GROUP BY clasue in SQL is a different beast than the group > attribute in It is, and I think it's more what you're looking for. You don't have to order a group by. By default, depending on your query, it would order it the way it was entered. SQL is really quite awesome, and a super huge thing for CF devs to know about. I can't state enough how much simpler life is when you know about groups and joins and whatnot. Groups and joins are some of the most complex queries you will build, but the performance gain is such that it's WELL worth digging in and learning what's possible. The idea is that the DB can do a lot of stuff WAY faster than CF can. That's what it's designed for. Not utilizing it, and re-inventing the wheel within your CF code is asking for lengthy CF code and slow execution. SQL's also a sticky part, and a good place for abstraction. I can appreciate Isaac's SQL stuff. There are a TON of nifty features in MySQL that plain aren't available in MSSQL, and vice versa, so unless you are organized, switching between the two can be a bitch. 'specially if you don't have your queries all in one spot, or at least sorta separate. The performance gains when using said "proprietary" functions can be significant. Not to mention ease of use. The MySQL group_concat() is freaking awesome, as is find_in_set(). They can make life easy, until you have to switch to some other DB (oracle has some great functions as well). I guess it boils down to the thing about linux and filesystems, and what you're after. It's the whole optimization morass, where optimization refers to speed, but depending on the frame of reference, you get totally different output. One super huge gain in one area doesn't do ANY good if it drags down another area more than the total "time saved" (or even the same amount, really). If you're looking at a 20 year frame, what's important is pretty different than if you're looking at 2 years. Keeping both in your head at once is a trip, and brings to mind the whole "flying a helicopter" analogy. (I must add that I love my Blade CP, an RC heli). Eh. Guess I could have kept this short and said "look into SQL"... Bah. I think the real problem is one of quantum physics, there is no "objective" reality. Impossible to achieve, by some crazy conundrum of existence. So we make do. We pretend that there is such a thing as objectivity, for the most part. But it's a sham! ;-) I did enjoy the "leaky abstraction" article, it did say (much betterly, I might add ;) much of what I was getting at. Good stuff to think about. It didn't come off as saying OO wasn't worthy tho... just that the truth lies somewhere between you and me. The logic seemed sound. Pragmatic, even. I guess articles like those are kind of Gödel-ish: We had it figured out man! Why'd you go and do that! =] ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237645 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: Group - but with no order specified
Correct, but if a filed being 'GROUPED' in CF is not ORDERed in SQL, then disinct items in the 'outer loop' may beoutput more than once. For example: #name# --#item# Will give you: Bob --book --ball Jane --book Mary --dress Jane --shoe Bob --bat Also the GROUP BY clasue in SQL is a different beast than the group attribute in On 4/11/06, Everett, Al (NIH/NIGMS) [C] <[EMAIL PROTECTED]> wrote: > > CF doesn't care if the records are actually in order, it just processes > the outer loop when there's a change of value. > > -- Scott Stroz Boyzoid.com ___ Some days you are the dog, Some days you are the tree. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237467 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: Group - but with no order specified
If he's typing it in in "order" then why not just order it by the autointeger field? -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 2:53 PM To: CF-Talk Subject: Re: Group - but with no order specified > I'm not trying to sound like a wiseass, but just point out a > perspective: if the client doesn't care what order they're in, what's > the harm in ordering them? Client wants it in the order he typed it in, not an alpha sort or anything. What the client don't get though is that he's not necessarily typing stuff in grouped together either. So, I'll give them a "sort by" box and make it their problem to define the sort. If "sort by" ain't defined, they'll get alpha, like it or not. It's been alpha for the last year anyway Just for reference, it's the list of "contacts" in the right panel here: http://www.nmrs.com/news/nelson-mullins-newsletter_detail.cfm?id=2406090D-BC D2-35D3-D151001A53DF4C73 What's wrong with an alpha sort? I've *no* idea! Either way, I'm happy to take more of their money!!! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237466 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: Group - but with no order specified
> I'm not trying to sound like a wiseass, but just point out a > perspective: if the client doesn't care what order they're in, what's > the harm in ordering them? Client wants it in the order he typed it in, not an alpha sort or anything. What the client don't get though is that he's not necessarily typing stuff in grouped together either. So, I'll give them a "sort by" box and make it their problem to define the sort. If "sort by" ain't defined, they'll get alpha, like it or not. It's been alpha for the last year anyway Just for reference, it's the list of "contacts" in the right panel here: http://www.nmrs.com/news/nelson-mullins-newsletter_detail.cfm?id=2406090D-BCD2-35D3-D151001A53DF4C73 What's wrong with an alpha sort? I've *no* idea! Either way, I'm happy to take more of their money!!! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237464 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: Group - but with no order specified
If you've got access to mySQL then just ORDER BY RAND(). -Original Message- From: Ken Ferguson [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 3:01 PM To: CF-Talk Subject: Re: Group - but with no order specified It may well be that they don't "not care". They may be specifically asking that they not be in any sort of discernible order. Why are you using the group in the cfoutput instead of in the query? --Ferg Joe Rinehart wrote: >> If I add a ORDER to the query, it obviously works - but, the client >> doesn't WANT them in any order. He just wants the groups together. >> > > I'm not trying to sound like a wiseass, but just point out a > perspective: if the client doesn't care what order they're in, what's > the harm in ordering them? > > If that doesn't satisfy them, maybe try an ORDER BY on the primary key > of whatever's giving you myGroup. > > -Joe > > -- > Get Glued! > The Model-Glue ColdFusion Framework > http://www.model-glue.com > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237463 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: Group - but with no order specified
It may well be that they don't "not care". They may be specifically asking that they not be in any sort of discernible order. Why are you using the group in the cfoutput instead of in the query? --Ferg Joe Rinehart wrote: >> If I add a ORDER to the query, it obviously works - but, the client >> doesn't WANT them in any order. He just wants the groups together. >> > > I'm not trying to sound like a wiseass, but just point out a > perspective: if the client doesn't care what order they're in, what's > the harm in ordering them? > > If that doesn't satisfy them, maybe try an ORDER BY on the primary key > of whatever's giving you myGroup. > > -Joe > > -- > Get Glued! > The Model-Glue ColdFusion Framework > http://www.model-glue.com > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237462 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: Group - but with no order specified
> If I add a ORDER to the query, it obviously works - but, the client > doesn't WANT them in any order. He just wants the groups together. I'm not trying to sound like a wiseass, but just point out a perspective: if the client doesn't care what order they're in, what's the harm in ordering them? If that doesn't satisfy them, maybe try an ORDER BY on the primary key of whatever's giving you myGroup. -Joe -- Get Glued! The Model-Glue ColdFusion Framework http://www.model-glue.com ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237461 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: Group - but with no order specified
> I'd suggest creating a SORT_ORDER (INTEGER) column for > each of the myGROUP records and ask your client what order he'd like > them in. This is probably what's going to have to happen. Otherwise, Yoda says, "Solutions there are, but convoluted are they!". ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237460 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: Group - but with no order specified
CF doesn't care if the records are actually in order, it just processes the outer loop when there's a change of value. If myGROUP has a numeric primary key you could do your ORDER BY on that, rather than on the name of it. Failing that, you could sort in some strange way that might look random, but isn't. Like on the 2nd through 5th character in the name. Failing THAT, I'd suggest creating a SORT_ORDER (INTEGER) column for each of the myGROUP records and ask your client what order he'd like them in. -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 11, 2006 3:00 PM To: CF-Talk Subject: Group - but with no order specified Is there a way to group output in a query and keep the groups together, but with no order specified anywhere? Select myGROUP, mySubGROUP from myTABLE 1. myGROUP a. mySUBGOUP 1 b. mySUBGOUP 2 Outputs (depending on what's in the database) 1. myGROUP a. mySUBGOUP 1 2. myGROUP 2 a. mySUBGOUP 1 b. mySUBGOUP 2 1. myGROUP b. mySUBGOUP 2 If I add a ORDER to the query, it obviously works - but, the client doesn't WANT them in any order. He just wants the groups together. I say you can't have it both ways. You have to have a sort order to keep the groups together. Or is there a weird work around for this? Yea - stupid I know, but clients want what they want... ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237458 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: Group - but with no order specified
I don't know what DB you're using, but have you tried using 'group by'? Something like this: select fname, lname, address, city, state, zip from contacts group by state, city, zip > -Original Message- > From: Les Mizzell [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 11, 2006 1:00 PM > To: CF-Talk > Subject: Group - but with no order specified > > Is there a way to group output in a query and keep the groups > together, > but with no order specified anywhere? > > > Select myGROUP, mySubGROUP from myTABLE > > > I need output like: > > 1. myGROUP > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > 2. myGROUP 2 > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > If you just do the below, it doesn't keep the groups together since > they're not together in the database... > > > 1. myGROUP > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > > Outputs (depending on what's in the database) > > 1. myGROUP > a. mySUBGOUP 1 > > 2. myGROUP 2 > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > 1. myGROUP > b. mySUBGOUP 2 > > > If I add a ORDER to the query, it obviously works - but, the client > doesn't WANT them in any order. He just wants the groups together. > > I say you can't have it both ways. You have to have a sort > order to keep > the groups together. Or is there a weird work around for this? > > > Yea - stupid I know, but clients want what they want... > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237457 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: Group - but with no order specified
This idea may seem complicated, but it should work. Put the ORDER BY in the SQL. Create an empty array. Do your cfoutput with the 'group' attribute. Use cfsavecontent to put each Group's output into a variable in the array. Then you can use some kind of randomizer to grab the output from the array. This would have to be a randomizer that doesn't allow repeating (forget the technical term for that). Good luck. On 4/11/06, Les Mizzell <[EMAIL PROTECTED]> wrote: > > Is there a way to group output in a query and keep the groups together, > but with no order specified anywhere? > > > Select myGROUP, mySubGROUP from myTABLE > > > I need output like: > > 1. myGROUP > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > 2. myGROUP 2 > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > If you just do the below, it doesn't keep the groups together since > they're not together in the database... > > > 1. myGROUP > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > > Outputs (depending on what's in the database) > > 1. myGROUP > a. mySUBGOUP 1 > > 2. myGROUP 2 > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > 1. myGROUP > b. mySUBGOUP 2 > > > If I add a ORDER to the query, it obviously works - but, the client > doesn't WANT them in any order. He just wants the groups together. > > I say you can't have it both ways. You have to have a sort order to keep > the groups together. Or is there a weird work around for this? > > > Yea - stupid I know, but clients want what they want... > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237456 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: Group - but with no order specified
Only order the groups, not the subgroups? On 4/11/06, Les Mizzell <[EMAIL PROTECTED]> wrote: > Is there a way to group output in a query and keep the groups together, > but with no order specified anywhere? > > > Select myGROUP, mySubGROUP from myTABLE > > > I need output like: > > 1. myGROUP > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > 2. myGROUP 2 > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > If you just do the below, it doesn't keep the groups together since > they're not together in the database... > > > 1. myGROUP > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > > Outputs (depending on what's in the database) > > 1. myGROUP > a. mySUBGOUP 1 > > 2. myGROUP 2 > a. mySUBGOUP 1 > b. mySUBGOUP 2 > > 1. myGROUP > b. mySUBGOUP 2 > > > If I add a ORDER to the query, it obviously works - but, the client > doesn't WANT them in any order. He just wants the groups together. > > I say you can't have it both ways. You have to have a sort order to keep > the groups together. Or is there a weird work around for this? > > > Yea - stupid I know, but clients want what they want... > > > ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:237455 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