asked on

MAX Function for an Over() results

I'm trying to simply take the max from a list created from an OVER()

From the query:

SELECT DISTINCT productCode,baseProductDescription, COUNT(*) OVER(PARTITION BY baseProductDescription) AS DescriptionCount
FROM dbo.BaseProducts
WHERE productcode NOT LIKE '[A-Z]%' 
ORDER BY productcode, baseProductDescription[code]

Open in new window

[/code]

I get my results I want in the DescriptionCount column. So now i want to take the max from that column. I'm having a hard time getting this because i need the Order by and it won't let me create my CTE with it. Simply using the Max(BaseProductDescrition) wont give me the results i want.

This may be simpler than i think.

Ryan Chong

see if this worked for you?

;with cte as
(
SELECT DISTINCT productCode,baseProductDescription, COUNT(*) OVER(PARTITION BY baseProductDescription) AS DescriptionCount
FROM dbo.BaseProducts
WHERE productcode NOT LIKE '[A-Z]%' 
)
select top 1 * from cte
ORDER BY DescriptionCount desc, productcode, baseProductDescription desc

Open in new window

Southern_Gentleman

ASKER

This is the results that it brought back

I'm looking to bring back

Productcode BaseProductDescription Count
0018 Tax Exempt 159
1000 JET A 255
10010 JET A 255
Etc etc.

It should be the max for each productcode

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Southern_Gentleman

ASKER

thanks Ryan, I guess I just had to go one CTE deeper.