选择具有MAX值的记录 [英] Selecting a Record With MAX Value

问题描述

在SQL Server 2008中，我有一个表 CUSTOMERS ，该表具有两列：

ID，
BALANCE

我该如何编写查询来选择余额最大的客户的ID， 以最有效的方式 ？

选项1：按余额订购和选择TOP（1）->费用太高。 / p>

选项2：首先获取最大金额，然后进行另一个查询，使用中的金额where子句->花费太多，而且似乎不可靠。

解决方案

注意：此答案的错误修订被删除。请查看所有答案。

WHERE 子句中的子选择可检索最大的 BALANCE 汇总了所有行。如果多个 ID 值共享该余额，则将全部返回。

 从客户中选择
 ID，
余额
 
余额=（从客户中选择MAX（余额）MAX）
  

In SQL Server 2008 I have a table CUSTOMERS that has two columns as:

ID, BALANCE

How can I write the query that selects the ID of the customer who has maximum balance, "in the most effective way"?

Option 1: ORDER BY BALANCE and SELECT TOP(1) --> costs too much.

Option 2: Firstly Get MAX amount, then make another query that uses the amount in where clause --> costs too much and not seem reliable.

解决方案

Note: An incorrect revision of this answer was edited out. Please review all answers.

A subselect in the WHERE clause to retrieve the greatest BALANCE aggregated over all rows. If multiple ID values share that balance value, all would be returned.

SELECT 
  ID,
  BALANCE
FROM CUSTOMERS
WHERE BALANCE = (SELECT MAX(BALANCE) FROM CUSTOMERS)

这篇关于选择具有MAX值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！