Question About Getting Max Dense_Rank Number

Question

Post reply

Question About Getting Max Dense_Rank Number

copling

Say Hey Kid

Points: 696
More actions
May 13, 2016 at 1:36 pm

#326357

I have the following issue: I'm trying to get the MAX number from a dense_rank query. Basically, I tried wrapping MAX around my DENSE_RANK function but SQL SERVER give me the following error Windowed functions cannot be used in context of another windowed function or aggregate.Here is the code for the table and data.
CREATE TABLE [dbo].[SalesItem2](
[ID] [int] NULL,
[SalesDate] [datetime] NULL,
[ProductID] [int] NULL,
[Qty] [int] NULL,
[TotalSalesAmt] [money] NULL
) ON [PRIMARY]
GO
INSERT INTO SalesItem2(ID,SalesDate,ProductID,Qty,TotalSalesAmt)
SELECT 1, '2014-10-01', 1, 1, 21.99
UNION
SELECT 2, '2014-10-02', 3, 1, 1.96
UNION
SELECT 3, '2014-10-03', 3, 10, 19.90
UNION
SELECT 4, '2014-10-03', 1, 2, 43.98
UNION
SELECT 5, '2014-10-02', 1, 1, 43.98
So, what I've done is the following to get the results that I want.
DECLARE @myvalue INT
SELECT @myvalue = DENSE_RANK() OVER(ORDER BY TotalSalesAmt)
FROM SalesItem2
PRINT @myvalue
My question is, is this the best way to accomplish my goal? My thoughts is that the assignment will assign the myvalue variable the last ranking value. Is there a better way to accomplish getting the max ranked number?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Have you tried COUNT(DISTINCT )?

SELECT myvalue = COUNT( DISTINCT TotalSalesAmt)

FROM SalesItem2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

copling Say Hey Kid Points: 696 More actions · Answer 2

Luis: That's a viable option. Do you see any harm in doing it the way that I have it? or do you think using Count DISTINCT is better for readability?

copling Say Hey Kid Points: 696 More actions · Answer 3

Actually, that's not going to work because if the TotalSalesAmt contains a NULL it's not going to be counted; and I need it to be counted. In the DENSE_RANK it'll get ranked as a low value which is what I want.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 4

This might work,

DECLARE @myvalue INT

SELECT @myvalue = MAX(COL_ONE)

FROM (SELECT DENSE_RANK() OVER(ORDER BY TotalSalesAmt) COL_ONE FROM SalesItem2) TEMP_ONE

drew.allen SSC Guru Points: 76985 More actions · Answer 5

copling (5/13/2016)
Actually, that's not going to work because if the TotalSalesAmt contains a NULL it's not going to be counted; and I need it to be counted. In the DENSE_RANK it'll get ranked as a low value which is what I want.

I'm having trouble imagining why you would ever want a SalesItem record to have a NULL TotalSalesAmt. If you don't have to account for NULLs and you don't have an index on your TotalSalesAmt, then Luis' approach will generally be much faster, because it will use a hash match whereas the DENSE_RANK requires you to sort the data to determine the DENSE_RANK.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

copling Say Hey Kid Points: 696 More actions · Answer 6

Drew: I agree but the data I'm actually working with can contain NULL values. I didn't create this schema but it's what I'm working with.

copling Say Hey Kid Points: 696 More actions · Answer 7

copling

Say Hey Kid

Points: 696

May 13, 2016 at 2:44 pm

#1879054

ZZartin: Okay, I like that. Very Interesting.