The blog has moved to the new site F10Debug.com
Showing posts with label Common table expression. Show all posts
Showing posts with label Common table expression. Show all posts

Sunday, May 8, 2016

How to get difference of top 2 records of each group in SQL Server order by date

I went though today Codility online exam for one of the top investment banking client interview, and I found one interesting question on SQL as below,


Question: 

Get top 2 records of each group /category in SQL Server, order by date and then get difference between them by category wise


In SQL Terms:

Retrieve difference between top 2 records of each group in sql server, order by date OR


Get difference of top 2 records of each group/category in SQL Server, order by date OR


Select top 2 records for each category in sql server order by date


Table Given:

CREATE TABLE [dbo].[test](
[event_type] [int] NOT NULL,
[value] [int] NOT NULL,
[date] [datetime] NOT NULL
) ON [PRIMARY]


Table Values 




Solution:

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY event_type ORDER BY date DESC) AS rn
   FROM test
)

-- Now select from cte.



now retrieve top 2 elements of each group and get difference between them as below,

SELECT event_type, (max(value)-min(value)) as v FROM cte
WHERE rn <= 2
group By event_type

And here goes,