SQL query to select records with highest Date using Sub Query in SQL Server

Last Reply on Nov 14, 2017 02:43 AM By kalpesh

Posted on Nov 14, 2017 12:23 AM

Hi,

I have promotion table. From Promotion table get max value by date.

Get on Max(Prom_Date)

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 looking for is

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

Thanks

Basit.

You are viewing reply posted by: kalpesh on Nov 14, 2017 02:43 AM.
Posted on Nov 14, 2017 02:43 AM Modified on on Nov 14, 2017 02:44 AM

It’s based on your logic for get only single record from table or multiple records from maximum Prom_Date value.

SQL

--1) If you want just single record from Promotion1 based on maximum date then use below test query

SELECT top 1 * FROM Promotion1
ORDER BY Prom_Date DESC

--2) If you want more than one records from Promotion1 based on maximum date then use below test query

SELECT * FROM Promotion1
WHERE Prom_Date IN (SELECT TOP 1 Prom_Date FROM Promotion1
					ORDER BY Prom_Date DESC)