Christan Andersson wrote: >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? > There seems to be a little confusion here regarding grouping and ordering. What do you mean by "the one I receive"? Do you only expect one? Or do you expect articles sorted by language in some order?
It looks like what you are *REALLY* trying to do is to sort by ID first, collecting all the articles of one ID together, and within those groups, sort by a language. No? You really want: select * from articles ORDER BY id, language ... GROUPing is the act of collecting rows together using collection functions like COUNT(), MAX(), etc., based on a grouping criterion. E.g. if you wanted a result of count of articles for each article ID, you'd do something like select count(*) from articles GROUP BY id; By definition, such a query returns *one* row for each *distinct* value represented by the set of group-by keys. Any columns that you have in your "select" statement that are not collection expressions must be in the GROUP BY clause. If this is not obvious to you, read an introductory database book for a good explanation of these basic concepts.. So why don't we do an ORDER before a GROUP? The problem (or fact) here is that grouping is inherently an order-destroying operation. When you group columns, you are inherently sorting and coalescing the data, but using buckets determined by the group-by keys. The result is one row per bucket that has data. It's meaningless to sort before the group-by, because the group-by *is* a sort of another kind. *After* you finish grouping the results, you can re-sort the groups based on whatever criteria you pick. -- Shankar. --------------------------------------------------------------------- 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
