CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008select
Dinesh Reddy Alla
Dinesh Reddy Alla

Reputation: 1717

Got Error in Retrieving results using group by and order by in SQL server 2008

I am have a table with name Scripts which contains data of 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 added records into table as shown below. These are just sample records.

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 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 Output:

ID     Action
3      Deleted
5      Updated
6      Created

Upvotes: 3

Views: 82

Answers (4)

Dgan
Dgan

Reputation: 10275

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

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

TRY THIS,

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

Upvotes: 0

Vishal Gajjar
Vishal Gajjar

Reputation: 1019

;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 last [ID] for each [Name].

Upvotes: 1

Arunprasanth K V
Arunprasanth K V

Reputation: 21901

Supposing if you set two of the Actions to be 'a' and 'z' .. then the result of select max(Actions) will be z

If u want the expected output then 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

Please go through this link you will get the concept

Upvotes: 0

Related Questions