GROUP BY返回第一条记录 [英] GROUP BY return the first record

问题描述

有没有解决GROUP BY第一条记录的方法？

我已经在SQL命令中设置了ORDER，我需要GROUP BY返回第一条记录，而不是最后一条。

编辑

以下是查询

  SELECT 
 DISTINCT（master.masterID），
 langData。*，
 master。* 
 FROM master_table as master 
 INNER JOIN lang_table as langData ON 
 langData.masterID = master.masterID 
 GROUP BY master.masterID 
 ORDER BY $ b $ CASE 
当langData.lang ='currentLang'那么1 ELSE 999 END，
 master.name desc LIMIT 0,10 
  

上面的查询选择 masterID 用于多语言表，并假设返回FIRST currentLang 中的记录，并按名称排序，然后排列所有其他语言。

不要问我为什么不设置th e语言。这是完成的方式。

因此，一切正常，迄今为止预计情况是我有一个记录与语言 en 和 fr 。如果 currentLang 是 en 则基于

  langData.lang ='currentLang'THEN 1 ELSE 999 END 
  

code> en 顺序为1， fr 顺序为999，而不是取得 en 我得到的值 fr 。

这就是为什么我想分组到第一行。

解决方案

我假设你正在谈论类似于

  SELECT * 
 FROM mytable 
 GROUP BY 
列
  

不应在 GROUP BY 中使用未分类的表达式，除非它们在组内完全相同。

如果你想返回一个表达式中最小值的记录，使用这个：

  SELECT mo 。* 
 FROM（
 SELECT DISTINCT列
 FROM mytable 
）md 
 JOIN myt能够mo 
 ON mo.id = 
（
 SELECT id 
 FROM mytable mi 
 WHERE mi.column = md.column 
 ORDER BY 
 mi.column，mi.someorder 
 LIMIT 1 
）
  

As far as I know mysql GROUP BY groups to the last record found.

Is there any solution to GROUP BY the first record?

I have setup the ORDER in SQL command and I need GROUP BY return the first record and not the last.

EDIT

Here is the Query

SELECT 
  DISTINCT(master.masterID),
  langData.*,
  master.* 
FROM master_table as master 
INNER JOIN lang_table as langData ON 
langData.masterID=master.masterID 
GROUP BY master.masterID 
ORDER BY 
CASE 
    WHEN langData.lang='currentLang' THEN 1 ELSE 999 END , 
    master.name desc LIMIT 0,10 

The query above select the masterID for multi language table and suppose to return FIRST the records in currentLang and order them by name AND THEN all other languages.

Don't ask me why I don't set the language in JOIN. This is the way to be done.

So everything works fine so far expect the scenario that I have a record with languages en and fr. If currentLang is en then based on

langData.lang='currentLang' THEN 1 ELSE 999 END

the en order is 1 and fr order is 999 and instead of getting the value of en I get the value of fr.

That's why I want to group to the first row.

解决方案

I assume you are talking of something like

SELECT  *
FROM    mytable
GROUP BY
        column

You shouldn't use unaggregated expressions in GROUP BY unless they are all same within the group.

If you want to return the record holding the least value of an expression within a group, use this:

SELECT  mo.*
FROM    (
        SELECT  DISTINCT column
        FROM    mytable
        ) md
JOIN    mytable mo
ON      mo.id = 
        (
        SELECT  id
        FROM    mytable mi
        WHERE   mi.column = md.column
        ORDER BY
                mi.column, mi.someorder
        LIMIT 1
        )

这篇关于GROUP BY返回第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！