Max(Row_Number() - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Max(Row_Number()

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-01-20 : 10:30:41

Hi

Im trying to add up all the rows for each category which Ive done below but then I want to only show in the CTE the total of all categories and not all the counts, so just the max total for each one.

The CTE is showing more than one category per max count on the Max(MyRank). Is there a way to keep the CTE but get it to show me only the max total for each single category?
Thanks

Code to work out totals

SELECT
[TypeOfIncident]
,[Category]
,ROW_NUMBER()OVER (PARTITION BY [Category] order by [Category]) 'MyRank'
--,[SubCategory]

FROM [dbo].[Incident]
WHERE [CreatedDateTime] BETWEEN '2013-01-20 00:00:00' AND '2014-01-20 23:59:59'
and [TypeOfIncident] = 'Failure'

Code to work out Max

USE Test

go

WITH CTE_MyRank
as
(
SELECT
[TypeOfIncident]
,[Category]
,ROW_NUMBER()OVER (PARTITION BY [Category] order by [Category]) 'MyRank'
--,[SubCategory]

FROM [dbo].[Incident]
WHERE [CreatedDateTime] BETWEEN '2013-01-20 00:00:00' AND '2014-01-20 23:59:59'
and [TypeOfIncident] = 'Failure'
)
SELECT TypeOfIncident,
(select distinct[Category]),
Max(MyRank) as MaxTotal

FROM CTE_MyRank

GROUP BY
[TypeOfIncident],
[Category],
[MyRank]
)

SZ1
Please help me to enable me to help others!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-20 : 22:29:04

can you post some sample data and the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-01-21 : 05:04:54

Sure I can get the max for each by using a count of and group by but wanted to know if I can do the same on a max Row_Number as shown below, currently the MaxTotal is returning a 1 instead of the actual max per category.

Id like to see the MaxTotal return the same as Category Total?
Ta

USE Test

go

WITH CTE_MyRank
as
(
SELECT
[TypeOfIncident]
,Count([Category]) 'Category'
,ROW_NUMBER()OVER (PARTITION BY [Category] order by [Category]) 'MyRank'
--,[SubCategory]

FROM [dbo].[Incident]
WHERE [CreatedDateTime] BETWEEN '2013-01-20 00:00:00' AND '2014-01-20 23:59:59'
and [TypeOfIncident] = 'Failure'
Group By [TypeOfIncident],
[Category]
)
SELECT TypeOfIncident,
[Category],
max(MyRank) as MaxTotal

FROM CTE_MyRank

GROUP BY
[TypeOfIncident],
[Category],
[MyRank]

--ORDER BY MyRank;

TypeOfIncident Category MaxTotal
Failure 1 1
Failure 2 1
Failure 14 1
Failure 26 1
Failure 347 1
Failure 748 1
Failure 1261 1
Failure 1289 1
Failure 1623 1
Failure 2877 1
Failure 3303 1
Failure 3394 1

SZ1
Please help me to enable me to help others!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 07:15:02

so according to your rules what should you get as values for MaxTotal field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-01-21 : 08:54:00

The same figure to the left of the 1 values, when you run the query without the CTE the rows will add numbers tallying to the amount per category, so I thought I could return the Max of each row number max output. SO if there are 10 oranges then I would see 1,2,3,4,5,6,7,8,9,10 and if 3 apples I would see 1,2,3, what I want to see in the column would be 10 and 3. This works with the count and group by as seen on the output but the right column with values of 1 I want the same output if this is possible. Its really for future use as it works with the count...thanks

SZ1
Please help me to enable me to help others!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources