Im trying to make a "data" system that is language-independent, ie the data stored can have any language therefore the same information is stored several times but with different values (depending on language)
lets say that I have this table.. articles(id,language,name,description) what I would like to do is retrieve 1 row per unique id in the chosen language select * from articles where language='en'; that is quite simple, unfourtunally, not all articles have the description or name written in english and the above query would not return these articles... PROBLEM is what I get. however doing a select * from articles group by (id); will return all articles even if they do not have an english translation, BUT here is the problem.. which language will be the one I recieve? Svedish? english? French? well I guess that the first one that is found inthe database will be the one retireved, so if I wrote the French translation before the english translation it would give me the french translation and not the english translation.. IF I however could somehow order the results before the group by was done, I could sort it so that first there would be english, then swedish, norwegian, etc... and at the bottom French ( :-) ) and then do the group by and the first language in this list would be the one I would get, English, and if there is no English I would get Swedish, and if there is no swedish, I would getnorwegian, and so on.. But since I cannot in mysql do an order by before an group by (will this be possible in a distant future?) the above sollution is not possible.. so what I do is the following select *,FIND_IN_SET(language,'no,dv,en') language2 from articlesorder by language2 desc; then ALL of these results are read into the application, and for each unique id I pulls out the first row, and forgets all other rows for that id. If the database could do that for me, I think it would be both faster and more efficient then what I'm doing now.. If I could do an group by on this one (I Know I cannot, but IF) select *,FIND_IN_SET(language,'no,dv,en') language2 from articlesorder by language2 desc group by id; I hope this explains better what I would like to do.. I just thought of something.... IF (and this I know nothing about) group by takes the first found row and skipps #2, #3, etc... (based an what to group by) is it then possible to take my query above (with the find in set functionallity) and insert the result in a temporary table so that the temporary table holds the ordered results and then do a select from that temporary table with an group by (and since the temporary table holds the ordered list and the group by returns the first row of each "group" I would get desired effect) hmmm,just tried it and I cant get it to work the insert into query complains about columns count (yes I have created 1 extr column for the find_in_set result and tried up to 4 extra columns but it does not work.. well my application works, I just want it faster and less to write (I have to make this loop for every table that is language dependent) and I'm not to stupid... the language-specific tables holds just that language specific data, (and the keys) the ordinary data that is not language specific, I have in a seperate table.. I hope this explains better what I want to do, and I would appreciate ANY ideas on how to do this better.. /Christian Andersson ----- Original Message ----- From: "M. A. Alves" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 13, 2001 12:58 PM Subject: Re: order by, group by > mysql > On Mon, 12 Nov 2001, Christian Andersson wrote: > > Is it possible in myslq to do an order by before an group by? > > For me it is not clear why would you want to do that. Can you give an > example? > > -- > , > M A R I O data miner, LIACC, room 221 tel 351+226078830, ext 121 > A M A D O Rua Campo Alegre, 823 fax 351+226003654 > A L V E S P-4150 PORTO, Portugal mob 351+939354002 > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
