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屋!