From Promotion table get max value by date

Question

Hi,

From Promotion table get max value by date

 

I have promotion table.

 

CREATE TABLE [dbo].[Promotion1](

[Prom_No] [numeric](18, 0) NULL,

[Prom_Date] [date] NULL,

[Prom_EmpName] [nvarchar](50) NULL,

[Prom_PromDesig] [nvarchar](50) NULL,

[Prom_PromSal] [numeric](18, 0) NULL,

[Prom_LastDesig] [nvarchar](50) NULL,

[Prom_LastSal] [numeric](18, 0) NULL

) ON [PRIMARY]

GO

data is given below.

 

INSERT INTO [UserDB].[dbo].[Promotion1]

([Prom_No]

,[Prom_Date]

,[Prom_EmpName]

,[Prom_PromDesig]

,[Prom_PromSal]

,[Prom_LastDesig]

,[Prom_LastSal])

VALUES

(1

,'2016-01-01'

,'alex'

,'HR2'

,200

,'HR1'

,100)

GO

INSERT INTO [UserDB].[dbo].[Promotion1]

([Prom_No]

,[Prom_Date]

,[Prom_EmpName]

,[Prom_PromDesig]

,[Prom_PromSal]

,[Prom_LastDesig]

,[Prom_LastSal])

VALUES

(2,

'2016-06-01'

,'alex'

,'HR3'

,400

,'HR2'

,200)

GO

The result should be below.

 

Prom_No	Prom_Date	Prom_EmpName	Prom_PromDesig	Prom_PromSal	Prom_LastDesig	Prom_LastSal
2	01-06-2016	alex	HR3	400	HR2	200