GROUP BY with preferences based on another FIELD

Is there a way to do a group by but givinig more importance to record that got a certain value in a field?

Example:

value - language - externID
1       default      10
1       english      10

2       english      10

3       default      10

Result of the SELECT:

1 - english - 10
2 - english - 10
3 - default - 10

IE: when there is the record with same "value" prefer th开发者_如何学Ce record with "english"

example: SELECT * FROM tbl WHERE externID = 10 AND language IN ('default','english') GROUP BY value "PREFER lang=english"

ps. "english" is an example I need I can choose the specific language between vary

---

You can use a case when statement:

http://dev.mysql.com/doc/refman/5.0/en/case-statement.html

Give a value to english that is lower than default, or make default absurdly high, then sort on that new name (e.g. language_value) and then the externID.

select language, enternalID,
case 
   when language = 'english' then 1 
   when language = 'default' then 100 
end case as langVal
from tableName
order by langVal, externalID

---

On the other question, Yes123 has added a bit extra...

If the prefered language is Chinese, and a value is asscoiated with default and english, that value should have no results returned...

I understand it to mean this...

• If the group contains the searched for language, return that language
• If the group does not contain that language, but contains default ONLY, return default
• If the group does not contain that language, but contain non-default languages, return nothing

DECLARE
  @preferred VARCHAR(64)
SET
  @preferred = 'English'

SELECT
  value,
  (
    SELECT
      language
    FROM
      tbl [search]
    WHERE
      externID  = 10
      AND value = tbl.value
      AND language IN ('default', @preferred)
    ORDER BY
      NULLIF(language, 'default') DESC
    LIMIT
      1
  )
FROM
  tbl
WHERE
  externID = 10
GROUP BY
  value
HAVING
  COUNT(*) = SUM(CASE WHEN language IN ('default', @preferred) THEN 1 ELSE 0 END)

The HAVING clause just states that every record for that value must either be 'default' or the language you're searching for.

The ORDER BY and LIMIT 1 in the sub query forces 'default' to be chosen last, always. Meaning that if the preferred language is found, that get shown in preferrence.

---

Building on Thyamine's idea, you could use a where clause that says the current row's language is the best language for the group:

select  value
,       language
,       externID
from    YourTable t1
where   case language when 'English' then 3 when 'default' then 2 else 1 end =
        (
        select  max(case language when 'English' then 3 
                        when 'default' then 2 else 1 end)
        from    YourTable t2
        where   t1.value = t2.value
                and t1.language = t2.language
                and t1.externID = t2.externID
        )
group by
        value
,       language
,       externID

To return no rows if neither a specific language nor a default is found, use:

case language when 'English' then 3 when 'default' then 2 else null end

Since null = null is untrue, this will not match the where clause.

For a generic version, which prefers any language over default, use:

case language when 'default' = 1 else 2 end

This rates default below anything "else".