I want the row which have max id. i need the rows which have id 2 and there.
My tables
please create tables using below script.
/****** Object: Table [dbo].[Customers] Script Date: 03/07/2017 14:48:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customers](
[CustomerID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Instalments] Script Date: 03/07/2017 14:48:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Instalments](
[id] [int] IDENTITY(1,1) NOT NULL,
[Amount] [int] NULL,
[Date] [date] NULL,
[CustomerID] [bigint] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Customers] ON
INSERT [dbo].[Customers] ([CustomerID], [Name]) VALUES (1, N'Muddassir ')
INSERT [dbo].[Customers] ([CustomerID], [Name]) VALUES (2, N'Irshad ')
SET IDENTITY_INSERT [dbo].[Customers] OFF
SET IDENTITY_INSERT [dbo].[Instalments] ON
INSERT [dbo].[Instalments] ([id], [Amount], [Date], [CustomerID]) VALUES (1, 500, CAST(N'2014-09-30' AS Date), 1)
INSERT [dbo].[Instalments] ([id], [Amount], [Date], [CustomerID]) VALUES (2, 600, CAST(N'2015-09-30' AS Date), 1)
INSERT [dbo].[Instalments] ([id], [Amount], [Date], [CustomerID]) VALUES (3, 800, CAST(N'2014-09-30' AS Date), 2)
SET IDENTITY_INSERT [dbo].[Instalments] OFF
My query
This is my query that i am using.
SELECT a.CustomerID,a.[Name],b.Amount,b.Date,b.id from Customers as a
left join Instalments as b On b.CustomerID=a.CustomerID
My Result this is the result of my query.
CustomerID Name Amount Date id
1 Muddassir 500 2014-09-30 1
1 Muddassir 600 2015-09-30 2
2 Irshad 800 2014-09-30 3
What result i want? but i want below result. i want all the rows which have max id only
CustomerID Name Amount Date id
1 Muddassir 600 2015-09-30 2
2 Irshad 800 2014-09-30 3