CODEWITHSUNDEEP
sql-serverstored-procedures
NAGARAJA H I
NAGARAJA H I

Reputation: 149

How to skip the max function which has only one entry when i do a group by in SQL Server

I have a requirement where I do a group by the table

Table

Name  salary
------------
abc   10000
abc    1000
def     100

Query:

select max(salary) 
from table 
group by Name

Result:

abc 10000
def   100

I don't want 'def' to be displayed since it's a single entry in the table. How can I achieve this?

Upvotes: 1

Views: 43

Answers (1)

S3S
S3S

Reputation: 25122

You can add a HAVING clause.

Having specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.

select 
   Name
  ,max(salary) 
from table 
group by Name having count(*) > 1

This will only return the aggregates for names that have more than 1 row, which seems to be what you want.

EXAMPLE

declare @table table (name varchar(16), salary int)
insert into @table
values
('abc',10000),
('abc',1000),
('def',100),
('xxf',100)

select 
    Name
    ,max(salary) 
from @table 
group by Name 
having count(*) > 1

Upvotes: 4

Related Questions