Clever Geek Handbook

Got an error in getting results using grouping by and on SQL Server 2008

I have a table named Scripts that contains the data of the modified procedures, functions and tables.

CREATE TABLE #Scripts
    (
    ID              NUMERIC (18) IDENTITY NOT NULL, 
    [Date]          DATETIME NULL,
    DatabaseName    VARCHAR (50) NULL,
    Name            VARCHAR (100) NULL,
    Type            VARCHAR (20) NULL,
    Action          VARCHAR (50) NULL,
    Description     VARCHAR (500) NULL,
    ModifiedBy      VARCHAR (50) NULL,      
    AddedTimestamp  DATETIME NULL,
    UpdateTimestamp DATETIME NULL,  
    )
GO

And I have added records to the table as shown below. These are just sample entries.

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_GetData', 'Stored Procedure', 'Created', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_GetData', 'Stored Procedure', 'Updated', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO


INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_GetData', 'Stored Procedure', 'Deleted', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_UpdateData', 'Stored Procedure', 'Created', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_UpdateData', 'Stored Procedure', 'Updated', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

INSERT INTO #Scripts ([Date], DatabaseName, Name, Type, Action, Description, ModifiedBy, AddedTimestamp, UpdateTimestamp)
VALUES ('2015-01-07 11:16:41.4', 'Test', 'sp_AddData', 'Stored Procedure', 'Created', 'To Get ActivitySubscriptions for Mobile from tblSubscriptions', 'dinesh.alla', '2015-01-07 11:39:39.703', '2015-01-07 11:39:39.703')
GO

I tried to get the results as shown below

SELECT MAX(ID) AS ID,MAX(Action) AS Action 
FROM #Scripts GROUP BY Name ORDER BY ID ASC

Output:

ID     Action
3      Updated
5      Updated
6      Created

Expected Result:

ID     Action
3      Deleted
5      Updated
6      Created
+3


source to share


4 answers


try this:

 select distinct sc.action,sc.ID from (
  SELECT MAX(ID)  OVER(PARTITION BY NAME  ) rn,*  FROM #Scripts  
  )d  join #Scripts sc
  on d.rn=sc.ID
 order by sc.ID


OUTPUT

action  ID
Deleted 3
Updated 5
Created 6
+1


source 


;WITH LatestChanges AS
(
    SELECT  S.[ID], S.[Date], S.[DatabaseName], S.[Name], S.[Type], S.[Action], S.[Description],
            S.[ModifiedBy], S.[AddedTimestamp], S.[UpdateTimestamp],
            [RowNum] = ROW_NUMBER() OVER (PARTITION BY S.[Name] ORDER BY S.[ID] DESC)
    FROM    [#Scripts] S
)
SELECT  *
FROM    LatestChanges
WHERE   [RowNum] = 1
ORDER BY ID


This returns the last [ID] for each [Name].

+1


source


Suppose you have set two of the actions: "a" and "z". then select max (Actions) will result in z

If u want the expected output you can try something like this

select TOP 1 MAX(ID) AS ID ,Action
FROM #Scripts where Name = 'sp_GetData'
GROUP BY Action
ORDER BY MAX(ID) DESC

Follow this link you will get the concept

0


source


TRY IT,

SELECT * FROM 
(
   SELECT *,
   ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY ID DESC )RN
   FROM @Scripts 
)T4
WHERE RN=1
0


source






More articles:


All Articles