Selecting a Record With MAX Value

tsql aggregate-functions
Ahmet Altun picture Ahmet Altun · Dec 5, 2011 · Viewed 202.4k times · Source

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.

Answer

Michael Berkowski picture Michael Berkowski · Dec 5, 2011

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)

Related questions

SQL Server: Difference between PARTITION BY and GROUP BY

I've been using GROUP BY for all types of aggregate queries over the years. Recently, I've been reverse-engineering some code that uses PARTITION BY to perform aggregations. In reading through all the documentation I can find about PARTITION BY, it …

Read More
Function to Calculate Median in SQL Server

According to MSDN, Median is not available as an aggregate function in Transact-SQL. However, I would like to find out whether it is possible to create this functionality (using the Create Aggregate function, user defined function, or some other method). …

Read More
Does T-SQL have an aggregate function to concatenate strings?

Possible Duplicates: Implode type function in SQL Server 2000? Concatenate row values T-SQL I have a view which I'm querying that looks like this: BuildingName PollNumber ------------ ---------- Foo Centre 12 Foo Centre 13 Foo Centre 14 Bar Hall 15 Bar Hall 16 Baz School 17 I …

Read More