> >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?
I know that using group by the way I talked about it is not really the way that group by is intended to work.. > 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? nope :-) since that would send to mych data from mysql to the application what I really want is the following.. let say that the table (id,language,name,description) where id,language is the primary key so that 1 id can have several languages the data in the database looks like this 1 'en' 'blue circle' 'this is a blue ' 1 'no' 'bla cirkel' 'dette er ei bla cirkel' 2 'en' 'green leaf' 'this is a green leaf from a tree' 2 'sv' 'grönt löv' 'detta är ett grönt löv från ett träd' if the language priority is en,sv,no the "select"would return the following 1 'en' 'blue circle' 'this is a blue ' 2 'en' 'green leaf' 'this is a green leaf from a tree' if however the language prority was sv,no,en the select should return the following 1 'no' 'bla cirkel' 'dette er ei bla cirkel' 2 'sv' 'grönt löv' 'detta är ett grönt löv från ett träd' since for id 1 there is no svedish translation it should choose the norwegian translation > You really want: > > select * from articles ORDER BY id, language ... this query would return all 4 rows, and my application would then have to discard 2 of them.. > > 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. which is what I want, I only want 1 versionof the id returned although the query can return many different id:s 1 id should only be representated once.. which is what group by does, however since mysql allows one to do a group by with less columns then selected, from which rows are those data that are not unique for example making a group by on my table on the id-column, which row is the language,name and description taken from.. IF i could controle which row that was, then group by would definitly work for me.. It looks like the data in those columns are the datafrom the first row that mysql encounters.. so what if I could affect which row would be first (for example doing a order by before the group by) then the query would do exaclty what I wanted it to do, even if the functionallity fo the functions were not intended for that usage to begin with (a feature) however it looks to me that I cannot control the order of the rows before the group by > 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.. This is not the case with mysql, and it is that functionallity that I was trying to use.. you can in mysql do a group by on less columns then what are in the select statement the problem is what data are presented in the columns that are not in the group by (se above for this) > 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. well using mysql:s type of order by, I see no problem in using an order by before group by since it would allow me to tell what to retrieve in the columns not in the group by.. I have solved the problem in 2 different ways already, one returns to many rows to the application and I have to in the application discard data (not good) the other is using temporary tables, and is working, but if it is working inthe next versionof mysql, I do not know, since it is based onthe fact that the group by is taking the first row:s data for columns that are not in the group by, and the insertion order in the temporary table.. but this is a pure Hack! if I could find 1 sql-query to do the work for me, I woul dbe extremely happy, but sofar I have not managed to find it /Christian Andersson --------------------------------------------------------------------- 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
