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]
