CODEWITHSUNDEEP
sqlsql-serverleft-join
Muddassir Irahad
Muddassir Irahad

Reputation: 41

How to get Max ID column of table two when join two table

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

Upvotes: 3

Views: 1624

Answers (3)

Henry Martens
Henry Martens

Reputation: 229

You could use MAX() to get the highest ID in a subquery and compare it with the current ID of Instalments. Try this:

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
WHERE b.id = (SELECT MAX(id) FROM Instalments AS i WHERE i.CustomerID = a.CustomerID);

Upvotes: 1

Esteban P.
Esteban P.

Reputation: 2809

are you sure you don't need always the most recent Instalments for each Customer?

This would be:

    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
       AND b.Date = (SELECT MAX(Date) 
                       FROM Instalments T 
                      WHERE T.CustomerID = b.CustomerID 
                    )

Upvotes: 1

user7715598
user7715598

Reputation:

Try This 

SELECT CustomerID
    ,[Name]
    ,Amount
    ,[Date]
    ,id
FROM (
    SELECT a.CustomerID
        ,a.[Name]
        ,b.Amount
        ,b.DATE
        ,b.id
        ,Row_NUmber() OVER (
            PARTITION BY a.CustomerID ORDER BY b.DATE DESC
            ) AS Rno
    FROM Customers AS a
    LEFT JOIN Instalments AS b ON b.CustomerID = a.CustomerID
    ) DT
WHERE DT.Rno = 1

Result

CustomerID  Name    Amount  Date    id
1   Muddassir   600 2015-09-30  2
2   Irshad      800 2014-09-30  3

Upvotes: 3

Related Questions