CODEWITHSUNDEEP
sql
Srinivasan
Srinivasan

Reputation: 12060

How to filter max value records in SQL query

I want to get all records except max value records. Could you pls suggest query for that.

For eg,(Im taking AVG field to filter)

SNO   Name     AVG
1     AAA       85
2     BBB       90
3     CCC       75

The query needs to return only 1st and 3rd records.

Upvotes: 0

Views: 10803

Answers (4)

Th3Nic3Guy
Th3Nic3Guy

Reputation: 1979

Use the below query:

select * from tab where avg<(select max(avg) from tab);

Upvotes: 5

Keval Doshi
Keval Doshi

Reputation: 748

select * from Sample where avg not in (select max(avg) from sample);

I think this should do

Upvotes: 1

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

Try this

   SELECT SNO, Name, AVG 
    FROM TableName 
    WHERE AVG NOT IN (SELECT  MAX(AVG) 
                      FROM TableName )

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460238

You could use a ranking function like DENSE_RANK:

WITH CTE AS(
    SELECT SNO, Name, AVG,
       RN = DENSE_RANK() OVER (ORDER BY AVG DESC)
    FROM dbo.TableName
)
SELECT * FROM CTE WHERE RN > 1

(if you are using SQL-Server >= 2005)

Demo

Upvotes: 3

Related Questions