CODEWITHSUNDEEP
sqlsql-server
sairfan
sairfan

Reputation: 1062

Need assistance to write query to get top number in a group

My table have following columns, (copy to create test table)

CREATE TABLE dbo.[PDNApprovalDetail](
    [PDNApprovalMasterID] [int] NOT NULL,
    [PDNMasterID] [int] NOT NULL,
    [PDNApprovalDetailID] [int] IDENTITY(1,1) NOT NULL,
    [DocumentToApproveID] [int] NULL,
    [RulesID] [int] NULL,
    [RuleSignatureTypeID] [smallint] NULL,
    [SignatureTypeID] [smallint] NULL,
    [ApprovalOrder] [smallint] NULL,
 CONSTRAINT [PK_PDNApprovalDetail] PRIMARY KEY CLUSTERED 
(
    [PDNApprovalDetailID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

-- use this insert statement

INSERT INTO [dbo].[PDNApprovalDetail]
           ([PDNApprovalMasterID]
           ,[PDNMasterID]
           ,[DocumentToApproveID]
           ,[RulesID]
           ,[RuleSignatureTypeID]
           ,[SignatureTypeID]
           ,[ApprovalOrder])
     VALUES
           (136
           ,125
           ,7
           ,2
           ,1
           ,0
           ,1)

data in my table may look like this (copy and paste to insert query)

136,    125,    7,  2,  1,  0,  1
135,    125,    7,  2,  1,  1,  1
136,    125,    7,  2,  1,  1,  1
135,    125,    7,  2,  0,  0,  1
137,    126,    7,  2,  1,  0,  1
137,    126,    7,  2,  1,  0,  1
139,    126,    7,  2,  0,  1,  1
139,    126,    7,  2,  0,  1,  1
138,    127,    7,  2,  1,  1,  1
140,    127,    7,  2,  0,  0,  1
140,    127,    7,  2,  0,  0,  1

i want the out put of query by grouping and getting only rows with MAX PDNApprovalMasterID, for group of 125 PDNMasterID, result should not contain rows with ID 135 for group 125 and so on, for group of 126 only row with MAX PDNApprovalMasterID 139, 137 should not be there, PDNMasterID 127 result row should contain 140 and should not contain 138

Look at query how to get sum of rest of columns, this is what im trying but not getting right results

SELECT DISTINCT pa.PDNMasterID, pa.DocumentToApproveID, pa.PDNApprovalMasterID
    , SUM(CASE WHEN pa.SignatureTypeID = pa.RuleSignatureTypeID THEN 1 ELSE 0 END) AS [Approved]
    , SUM(CASE WHEN pa.SignatureTypeID != pa.RuleSignatureTypeID THEN 1 ELSE 0 END) AS [Unapproved]
    , COUNT(*) AS RuleCount
    FROM dbo.PDNApprovalDetail pa
    WHERE pa.[DocumentToApproveID] = 7
    group by pa.PDNMasterID, pa.DocumentToApproveID, pa.PDNApprovalMasterID
    having pa.PDNApprovalMasterID = MAX(pa.PDNApprovalMasterID)

result of above query, its contains 135 PDNApprovalMasterID row which should not be there

PDNMasterID DocumentToApproveID PDNApprovalMasterID Approved Unapproved RuleCount
125                 7                   135             2         0         2
125                 7                   136             1         1         2

Upvotes: 1

Views: 92

Answers (1)

Robert
Robert

Reputation: 25753

Try add max to select statement

SELECT pa.PDNMasterID, pa.DocumentToApproveID, MAX(pa.PDNApprovalMasterID)
    , SUM(CASE WHEN pa.SignatureTypeID = pa.RuleSignatureTypeID THEN 1 ELSE 0 END) AS [Approved]
    , SUM(CASE WHEN pa.SignatureTypeID != pa.RuleSignatureTypeID THEN 1 ELSE 0 END) AS [Unapproved]
    , COUNT(*) AS RuleCount
    FROM dbo.PDNApprovalDetail pa
    WHERE pa.[DocumentToApproveID] = 7  
    and pa.PDNApprovalMasterID = (select MAX(pa1.PDNApprovalMasterID)
                                  from  dbo.PDNApprovalDetail pa1 
                                  where pa.PDNMasterID = pa1.PDNMasterID
                                   and pa.DocumentToApproveID = pa1.DocumentToApproveID
                                  ) 
    group by pa.PDNMasterID, pa.DocumentToApproveID;

SQL Fiddle DEMO

Upvotes: 1

Related Questions