get 1st row per group

Question

get 1st row per group

mtl20144102

Old Hand

Points: 394

April 15, 2019 at 1:03 am

CREATE TABLE [dbo].[EmployeeTest1](
[JobTitle] [varchar](50) NULL,
[BirthDate] [date] NULL,
[MaritalStatus] [char](1) NULL,
[Gender] [char](1) NULL,
[HireDate] [date] NULL
)

INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Senior Tool Designer','1974-12-23','S','M','2007-12-05')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Design Engineer','1952-09-27','M','F','2008-01-06')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Design Engineer','1959-03-11','M','M','2008-01-24')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Research and Development Manager','1987-02-24','M','M','2009-02-08')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Research and Development Engineer','1986-06-05','S','F','2008-12-29')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Research and Development Engineer','1979-01-21','M','F','2009-01-16')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Research and Development Manager','1984-11-30','M','M','2009-05-03')

How do you get the first row per group by JobTitle. Return this.

Senior Tool Designer 1974-12-23 S M 2007-12-05

Design Engineer 1952-09-27 M F 2008-01-06

Research and Development Manager 1987-02-24 M M 2009-02-08

Research and Development Engineer 1986-06-05 S F 2008-12-29

This topic was modified 5 years, 7 months ago by mtl20144102.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

mtl20144102 Old Hand Points: 394 More actions · Answer 1

CREATE TABLE [dbo].[EmployeeTest1](
[JobTitle] [varchar](50) NULL,
[BirthDate] [date] NULL,
[MaritalStatus] [char](1) NULL,
[Gender] [char](1) NULL,
[HireDate] [date] NULL
)

INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Senior Tool Designer','1974-12-23','S','M','2007-12-05')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Design Engineer','1952-09-27','M','F','2008-01-06')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Design Engineer','1959-03-11','M','M','2008-01-24')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Research and Development Manager','1987-02-24','M','M','2009-02-08')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Research and Development Engineer','1986-06-05','S','F','2008-12-29')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Research and Development Engineer','1979-01-21','M','F','2009-01-16')
INSERT INTO [dbo].[EmployeeTest1] ([JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate]) VALUES('Research and Development Manager','1984-11-30','M','M','2009-05-03')

How do you return the first row grouped by JobTitle? The resultset would be this.

Senior Tool Designer 1974-12-23 S M 2007-12-05

Design Engineer 1952-09-27 M F 2008-01-06

Research and Development Manager 1987-02-24 M M 2009-02-08

Research and Development Engineer 1986-06-05 S F 2008-12-29

This reply was modified 5 years, 7 months ago by mtl20144102.