blocks|key|3445277|text|SELECT+MAX(id),+subid+FROM+table+GROUP+BY+subid+ORDER+BY+subid+DESC|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3445278|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT MAX(id), subid FROM table GROUP BY subid ORDER BY subid DESC
</code></pre>

blocks|key|3445255|text|select+max(id),+subid+group+by+subid+order+by+subid+desc|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3445256|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select max(id), subid group by subid order by subid desc
</code></pre>

blocks|key|2754845|text|使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2754846|order+by+(col)+ASC+or+DESC|code-block|syntax|javascript|2754847|进行升序或降序|2754848|也使用|2754849|group+by+(col)|2754850|或|2754851|distinct|2754852|删除重复数据|2754853|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Use 

<pre><code>order by (col) ASC or DESC
</code></pre>

To have ascending or descending sorting

Use also 

<pre><code>group by (col)
</code></pre>

or

<pre><code>distinct
</code></pre>

To drop out duplicate datas

blocks|key|2754904|text|对“Geri”一词使用的解释。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2754905|已获接纳的答覆如下：|2754906|SELECT+*+FROM+table+GROUP+BY+subid+ORDER+BY+subid+DESC|code-block|syntax|javascript|2754907|查询选择按子by分组的所有列。在这种情况下，只有一个其他列(尽管在其他查询中可能有许多其他列)。GROUP+BY被设计用来处理聚合函数，例如MAX。使用MAX(+id+)，MySQL将查找每个子id的所有行，然后为每个子id找到id的最大值并返回该值。|2754908|因此，对于子id+2，只有一个id为3，这显然是该子id的id的最大值，并且将被返回，同样，对于3的子id，唯一的id是4。对于1的子id，id的可能值是1和2。因为2是最高的值，它将被返回。|2754909|如果没有聚合函数，MySQL可以自由地选择任何id值，并且它所选择的id值没有定义，并且可能发生更改。|2754910|因此，对于1的子id，它可能返回id+1或id+2。|2754911|如果SELECT子句中有列，这不是聚合函数的结果，并且在GROUP+BY子句中也没有提到，那么SQL的大多数类型都会产生错误。默认情况下，MySQL在这种情况下不会出错，这是有逻辑原因的。例如，对于完全依赖于GROUP子句中的列的额外列，例如根据唯一的用户id进行分组并提取用户名，这种行为是在SQL标准中定义的，尽管确切的实现是不完整的(当然，如果将这些直接相关的列添加到GROUP子句中，则当然不会出现问题)。|2754912|在这种情况下，可以将MySQL设置为错误，通过使用only_full_group_by设置运行使其更符合SQL的其他口味：-|2754913|by|offset|length|2754914|@franglais的回答：-|2754915|select+max(id),+subid+group+by+subid+order+by+subid+desc|2754916|避免未定义要返回的id值的问题，因为它指定返回的每个子id的最大id值将被返回。|2754917|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_only_full_group_by^0|0|0|0|0|0|0|0|0|0|0|2|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1D|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1E|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1F|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1G|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1H|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1J|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1K|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1L|8|@]|9|@$W|1M|X|1N|1|1O]]|A|$]]|$1|Y|3|Z|5|6|7|1P|8|@]|9|@]|A|$]]|$1|10|3|11|5|F|7|1Q|8|@]|9|@]|A|$G|H]]|$1|12|3|13|5|6|7|1R|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|1B]]]]

An explanation for @Geri of the use of GROUP BY.

with the accepted answer of :-

<pre><code>SELECT * FROM table GROUP BY subid ORDER BY subid DESC
</code></pre>

the query selects all columns grouped by subid. In this case there is just 1 other column (although in other queries there could be many other columns). GROUP BY is designed to work with aggregate functions, for example MAX. With MAX(id) MySQL will find all the rows for each subid and then find the maximum value of id for each subid and return that.

So for subid 2 there is only a single id of 3 so that is obviously the max value of the id for that subid and will be returned, similarly for subid of 3 the only id is 4. For subid of 1 the possible values of id are 1 and 2. As 2 is the highest value it will be returned.

Without an aggregate function MySQL is free to chose any value of id, and which one it choosing is not defined and may change.

So for subid of 1 it might return the id of 1 or the id of 2.

Most flavours of SQL will issue an error in the situation where there is a column in the SELECT clause which is not the result of an aggregate function and is also not mentioned in the GROUP BY clause. By default MySQL does not error in this situation, and there are logical reasons for this to be correct. For example with extra columns which are entirely dependent on a column that is in the GROUP BY clause - such as grouping by a unique user id and pulling back the users name, and this behaviour is defined in the SQL standards although exact implementation is patchy (and of course it becomes a non issue if these directly related columns are added to the GROUP BY clause).

MySQL can be set up to error in this situation, bringing it more in to line with other flavours of SQL by running with only_full_group_by set up:-

<a href="http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_only_full_group_by" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html#sqlmode_only_full_group_by</a>

The answer by @franglais of:-

<pre><code>select max(id), subid group by subid order by subid desc
</code></pre>

avoids the issue of which value of id to be returned being undefined, as it is specifying that for each subid returned the maximum id value for that subid will be returned.

Database ::

<ul>
<li>id subid</li>
<li>1 &nbsp; 1</li>
<li>2 &nbsp; 1</li>
<li>3 &nbsp; 2</li>
<li>4 &nbsp; 3</li>
</ul> 

The id column is automatically incremented and primary
subid is any number. 

I want to arrange subid in decreasing order, remove rows which consists
duplicate subid in it and get rows 

Result i want :: 

<ul>
<li>id subid</li>
<li>4 &nbsp; 3</li>
<li>3 &nbsp; 2</li>
<li>2 &nbsp; 1</li>
</ul>

Deselect duplicates in sql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

数据库：id子id1人-12.2.13.24.3id列自动递增，主子id为任意数字。我要按递减顺序排列subid，删除其中包含重复子it的行，然后获取行。我想要的结果：id子id4.33.22.2.1

问取消选择sql中的重复项
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问取消选择sql中的重复项EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问取消选择sql中的重复项
EN