Let’s first create a table with four fields.

  1. CREATE TABLE [dbo].[table1](
  2. [id] [int] NULL,
  3. [Name] [varchar](30) NULL,
  4. [Salary] [int] NULL,
  5. [DepId] [int] NULL
  6. )
Now insert some records into this table.
  1. INSERT table1 VALUES (1, 'test1', 1200, 1)
  2. INSERT table1 VALUES (2, 'test2', 1500, 1)
  3. INSERT table1 VALUES (3, 'test3', 1300, 2)
  4. INSERT table1 VALUES (4, 'test4', 2000, 3)
  5. INSERT table1 VALUES (5, 'test5', 1000, 2)
  6. INSERT table1 VALUES (6, 'test6', 1300, 2)
If you are using SQL Server 2008 r2 or above than you can use the following code.
  1. INSERT table1 VALUES (1, 'test1', 1200, 1), (2, 'test2', 1500, 1),(3, 'test3', 1300, 2),(4, 'test4', 2000, 3),(5, 'test5', 1000, 2),(6, 'test6', 1300, 2)
Select all records of the table.
  1. select * from table1
Output

table

Now write a query for the min and max salary by department:

  1. select depid, max(salary) as MaxSalary, min(Salary) as MinSalary from table1 group by DepId
Output

result

I hope you enjoy this article.

Happy coding.