Thanks for your help, but it is not giving the result. The problem is that when you make "GROUP BY id" then the row representing the group is the first row in the group, and adding "ORDER BY ..." after the group by part just orders the grouped rows, but the grouped rows are in the wrong languages, therefore ordering is not solving the problem. I need to get a row from the middle of the group and not the first default row in the group. Might be the solution lies in HAVING clause, but then I got 2 rows with same id...
-thanks, Eli ""Michael Shuler"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > > Try: SELECT * FROM table GROUP BY id ORDER BY language="en" DESC, language > DESC > > (I didn't set up your table on my sever to test this but this should work) > > See http://www.mysql.com/doc/en/Sorting_rows.html down at the bottom near > the user comments someone is doing the same kind of thing. > > Hope this helps! > Mike > > > > -----Original Message----- > From: ighen [mailto:[EMAIL PROTECTED] > Sent: Sunday, December 21, 2003 5:34 PM > To: Michael Shuler > Subject: Re: Getting a particular row from GROUP BY > > > > ----- Original Message ----- > From: "Michael Shuler" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: "'ighen'" <[EMAIL PROTECTED]> > Sent: Monday, December 22, 2003 1:18 AM > Subject: RE: Getting a particular row from GROUP BY > > > > What would be the rules for deciding if id 3 should be fr or de? > > if 'en' does not exist, then it does not matter which language is returned > (even be the first available language in the rows order in the DB). But > would be also helpful to know if say I want to retrieve one language from > this order: en, fr, de. -thanks, Eli > > > > > > Hello, > > > > I have the next table (the primary key is id+language): > > > > || id || language || title || > > --------------------------------------- > > 1 de {some_text} > > 1 en {some_text} > > 1 fr {some_text} > > 2 fr {some_text} > > 2 de {some_text} > > 2 en {some_text} > > 3 fr {some_text} > > 3 de {some_text} > > > > I want to group all rows with the same id, and sort the grouped rows > > with the title field of the 'en' language. I use the next query: > > > > SELECT * FROM table GROUP BY id,language HAVING language='en' ORDER BY > > title; > > > > This returns: > > 1, en > > 2, en > > How can I do that it will return me the following? > > 1, en > > 2, en > > 3, fr > > > > I also tried the next query: > > SELECT * FROM table GROUP BY id,language='en' ORDER BY title; but it > > returned me twice the id 1 and 2. > > > > thanks in advance, > > -Eli > > > > --- > > Incoming mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.554 / Virus Database: 346 - Release Date: 12/20/2003 > > > > > > --- > > Outgoing mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.554 / Virus Database: 346 - Release Date: 12/20/2003 > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.554 / Virus Database: 346 - Release Date: 12/20/2003 > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.554 / Virus Database: 346 - Release Date: 12/20/2003 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
