Last post Feb 13, 2015 04:57 AM by eralper
Feb 10, 2015 09:49 AM|Magesh.L|LINK
I need first maximum of ActualDate in 'A' similiarly in 'B' and in 'C' corresspoding record of each category
and also I need second maximum of ActualDate in 'A' similiarly in 'B' and in 'C' corresspoding record of each category
How to achieve in SQL Server(T-SQL / SQL)?
Feb 10, 2015 10:42 AM|limno|LINK
What do you expect from the sample data? Thanks.
Feb 11, 2015 03:17 AM|Michelle Ge - MSFT|LINK
So far as I know, if you want to dispaly the maxdate and second maxdate, I think you need to store the Id with the MaxDate into a temp table. Then you can try to select the maxDate without the Id which has been selected as the MaxDate.
Hope my description is useful for you.
Feb 11, 2015 06:50 AM|gimimex|LINK
Try something like this:
with CTE_RN as
ROW_NUMBER() OVER(PARTITION BY Category ORDER BY ActualDate DESC) as RN
select * from CTE_RN
where RN < 3
Hope this helps.
Feb 12, 2015 02:57 AM|Magesh.L|LINK
Thanks it works but can you explain me how it works since this is the first time I am using
Feb 12, 2015 06:28 AM|gimimex|LINK
Check the page below:
Feb 13, 2015 04:57 AM|eralper|LINK
Please check the below SQL SELECT statement
with cte as (
ID, Category, ActualDate, ROW_NUMBER() OVER (PARTITION BY Category ORDER BY ActualDate Desc) rn
select Category, Max(MaxDate) as MaxDate, Max(SecondMaxDate) as SecondMaxDate
case when rn = 1 then ActualDate else null end as MaxDate,
case when rn = 2 then ActualDate else null end as SecondMaxDate
where rn < 3
group by Category
I use the
SQL Row_Number() function for determining the max date and second max date for each category
Then I use
CASE statement and GROUP BY in CTE for pivot table solution to display these two values in one line
I hope this will be useful,