Given columns a, b, and c, where I GROUP BY a, how do I get the value of column b in each group which corresponds to the maximum value of column c?
I have a table which contains some translated records. Column a is the record id, column b is a translated field e.g. the title, and column c is a language code: +----+---------------------------------+---------+ | id | title | culture | +----+---------------------------------+---------+ | 1 | french title (record 1) | fr | | 2 | another french title (record 2) | fr | | 2 | spanish title (record 2) | es | [...] I want to get an ordered list of titles, in the user's preferred language. The catch is that a title may not be available in the user's preferred language, in which case I should fallback to another language, instead of displaying blank. So in this example, if the user prefers French, I want to get: +---------------------------------+ | title | +---------------------------------+ | another french title (record 2) | | french title (record 1) | [...] But if the user prefers Spanish, I want to get: +--------------------------+ | title | +--------------------------+ | french title (record 1) | | spanish title (record 2) | [...] I started with: SELECT title FROM my_table GROUP BY id ORDER BY title; In MySQL, selecting an un-grouped column seems to return an arbitrary value from that group, so in this case it returns a title in an arbitrary language. In other databases however, I gather it is invalid to select an un-grouped column: "When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column." http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-GROUPBY Some thoughts I had were using a CASE statement to order the rows according to the user's language preference: ... ORDER BY CASE culture WHEN 'es' THEN 1 ELSE 0 END ... - or using a WHERE clause to select only rows which are the maximum within their group: SELECT CASE culture WHEN 'es' THEN 1 ELSE 0 END AS weight ... WHERE weight = MAX(weight) ... However in the first case I'm not sure how to use ORDER BY to get the first row within a group, and in the second case I gather "WHERE weight = MAX(weight)" is an "invalid use of group function." Any suggestions how to get an ordered list of titles, with fallback if the title is not available in the user's preferred language? Or how to get the value of column b in each group which corresponds to the maximum value of column c? Much thanks, Jack
signature.asc
Description: This is a digitally signed message part
