title: How to get Max ID column of table two when join two table
body: <p>I want the row which have max id. i need the rows which have id 2 and there.</p>
  
  <p><strong>My tables</strong><br>
  please create tables using below script.  </p>
  
  <pre><code> /****** 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
  </code></pre>
  
  <hr>
  
  <p><strong>My query</strong></p>
  
  <p>This is my query that i am using.</p>
  
  <pre><code>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
  </code></pre>
  
  <hr>
  
  <p><strong>My Result</strong>
  this is the result of my query.</p>
  
  <pre><code>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
  </code></pre>
  
  <hr>
  
  <p><strong>What result i want?</strong>
  but i want below result. i want all the rows which have max id only</p>
  
  <pre><code>CustomerID  Name        Amount  Date        id
  1           Muddassir   600     2015-09-30  2
  2           Irshad      800     2014-09-30  3
  </code></pre>