SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Max(Row_Number()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 01/20/2014 :  10:30:41  Show Profile  Reply with Quote
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)

Singapore
17638 Posts

Posted - 01/20/2014 :  22:29:04  Show Profile  Reply with Quote
can you post some sample data and the expected result ?


KH
Time is always against us

Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 01/21/2014 :  05:04:54  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/21/2014 :  07:15:02  Show Profile  Reply with Quote
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
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 01/21/2014 :  08:54:00  Show Profile  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000