How to use max function with select top in sql

sql sql-server sql-server-2012

10,899

Solution 1

You can use CTE query. Example:

WITH CTEQuery (AccountId) AS (
    SELECT TOP 10 AccountId 
    FROM TempAccount
    ORDER BY AccountId
)
SELECT MAX(AccountId) 
FROM CTEQuery

Solution 2

Do the TOP 10 in a derived table, then use MAX on its result. Something like:

select max(dt.col1)
from
(
  select top 10 col1
  from table 
  where ...
  order by ...
) dt

10,899

Author by

nitinvertigo

Updated on July 17, 2022

Comments

nitinvertigo almost 2 years

I have a table, lets call it TempAccount, with a column named AccountID. It contains numbers from 1,2,3...and so on.

My requirement is that I should select the maximum value from the top 10 AccountIDs.

I know I can do it by creating a temp table and inserting the top 10 values in it and then select the maximum value out of that table. But I was hoping if there is any direct query I can use to achieve this.

Something like MAX(SELECT TOP 10 AccountID FROM TempAccount)

What is the best way I can achieve this?

Note: I am using SQL Server 2012
- James Z almost 9 years
  
  How do you define the top 10 accounts IDs?
- Anil almost 9 years
  
  Is maximum value a column in same table of any other table?
- nitinvertigo almost 9 years
  
  @wewesthemenace Assume the table has 1000 values. I want to select the top 10 in that and in that I want to select the max. If I directly use max it would give me the max of all the values in tha table
- nitinvertigo almost 9 years
  
  @AnilKumat same table
- nitinvertigo almost 9 years
  
  @JamesZ i mentioned in the question that the accountIDs are numbers from 1,2,3... Please note that it is just a sample. It can be random too
- James Z almost 9 years
  
  @nitinvertigo Selecting max from top 10 rows without any criteria what the top 10 is quite pointless because you don't know which rows the top 10 will actually select.
- nitinvertigo almost 9 years
  
  @JamesZ pls see my comment in the answered post. The data in the table is stored in my desired order. So I know which top 10 rows it will select
- James Z almost 9 years
  
  @nitinvertigo Populating data into a table in order does not guarantee that you will get it out in the same order. You will have to specify an order by to be sure
Oliver almost 9 years

I hope you are aware that the "TOP 10" rows returned by the CTE are not guaranteed? You didnt specify an ORDER BY, so TOP 10 can essentially return any 10 rows it likes.
nitinvertigo almost 9 years

@Oliver The data in TempAccount table is pre populated in the format I desire ( ascending order) So I do not need to specify the ORDER BY here in CTE
Oliver almost 9 years

@nitinvertigo: You are correct that your SQL Server version as of today behaves like that. I am aware that small SQL Server tables usually behave that way. Still, this behaviour is neither guaranteed by the SQL standard, nor by SQL Server documentation and you should not rely on it for at least 4 reasons. See the counterexamples here: sqlblogcasts.com/blogs/sqlandthelike/archive/2010/06/27/…
slayernoah about 5 years

You need to also use a ORDER BY clause. Otherwise the order of the results are not guaranteed even though they may have be inserted into the table in ascending order based on a field. Or even though you see it on the results of almost all the SELECT queries you do. This is clearly mentioned on the Microsoft Documentation: docs.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order.