> 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)
=Is this correct?
"data" ~ news reports/magazine articles
"article" ~ a news report (cf an item or product)
"language" = spoken language (cf computer probgramming)
"different values (depending on language)" = translations
=Is "name" that of the author or the title of the article?
=Is "id" a UNIQUE column, or if there are both English and French versions of the same
article do they have the
same id? Please describe "id".
> 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';
= this will select ALL of the articles written in English
- if there were two such articles with the same 'id', and in English, then they would
both appear
- unless all articles (id-s) have an English language translation, then this will not
produce a list of all
unique ids amongst the 'hits' (I assume this is not possible - right?)
=do you want ALL articles, or only the one with a matching id?
> 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,
=assuming that all the translations of the same article have the same id! The
following will achieve a similar
effect:
SELECT * FROM articles ORDER BY id [ASC];
> 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..
=you are correct here: "the first one that is found", which has more to do with the
way the rows are
indexed/retrieved by MySQL, rather than the order in which they were INSERTed.
> IF I however could somehow order the results before the group by was done, I
=or question the need to group the data - this clause is useful when you want to
further analyse the data in
those 'groups', but may not otherwise be useful. You do not appear to be using SQL to
analyse the groups, you
appear to only want the data collected/sequenced.
> 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..
=so let's add language into the sequencing instruction:
SELECT * FROM articles ORDER BY id, language;
=now this does NOT answer your question because an article with a full set of
translations will appear in the
sequence dv, en, fr, no, se.
=there are ways for you to dictate this sequence to be en, no, fr..., and if you're
interested I'll try to
recall the method...
> 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;
SELECT * FROM articles
WHERE language = 'no' (etc or set construct)
ORDER BY id;
> 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..
=indeed, the general rule is that doing calculations/organisations/presentations in
SQL will be faster than
post-processing the hits.
> 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;
SELECT * FROM articles
WHERE language = 'no' (etc or set construct)
ORDER BY id ASC, language DESC;
> I hope this explains better what I would like to do..
=why ALL the articles and not just one?
=what is your 'prime' language - or better, what is the order of language preference
(from most prefered to
least-used)
> 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..
=it's getting too complicated for me - but someone who's good at this stuff might be
able to solve it for you
without change.
=At this point I'm wondering if it would be easier to act cowardly and run away? How
about changing the language
column to a digit. Select which language is represented by which digit according to
your language preference.
Then the ASC | DESC will work cheerfully/easily!
> 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..
=Trying to help(?),
=dn
> ----- 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
>
>
---------------------------------------------------------------------
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
