CODEWITHSUNDEEP
sqlsql-serversql-server-2012
nitinvertigo
nitinvertigo

Reputation: 1180

How to use max function with select top in sql

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

Upvotes: 2

Views: 16386

Answers (2)

Fka
Fka

Reputation: 6234

You can use CTE query. Example:

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

Upvotes: 5

jarlh
jarlh

Reputation: 44766

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

Upvotes: 2

Related Questions