Row Numbering

Question

Post reply

Row Numbering

Jack Pulizzo

SSC Rookie

Points: 48
More actions
May 29, 2008 at 12:44 pm

#191084

Hello all,
I am having trouble getting the results I need from this query:
select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo ORDER BY TransTime) as CustCount
, ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount
, DriverNo
, Convert(VarChar(8), TransTime, 108) As TransTime
, Left(ToName,2) As AirPortCode
from transactions

Viewing 10 posts - 1 through 10 (of 10 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Jack - what exactly are you having trouble with? What is not happening?

Can't help you without some more specifics....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Pulizzo SSC Rookie Points: 48 More actions · Answer 2

Sorry, I accidentally posted before the message was complete.

So, I have this query:

select ROW_NUMBER() OVER(PARTITION BY TransTime, DriverNo ORDER BY TransTime) as CustCount

, ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo ORDER BY TransTime) As DelCount

, DriverNo

, Convert(VarChar(8), TransTime, 108) As TransTime

, Left(ToName,2) As AirPortCode

from transactions

The results I am trying to get would be like this:

CustCount DelCount DriverNo TransTime AirPortCode

-------------------- -------------------- -------- --------- -----------

1 1 042000 04:00:00 NW

1 2 042000 04:00:00 NW

1 1 042000 06:51:00 AA

1 1 042000 08:45:00 NW

1 1 042000 08:52:00 NW

1 1 042000 10:30:00 NW

1 1 042000 10:45:00 NW

2 1 042000 10:45:00 AA

1 1 042000 11:30:00 NW

1 2 042000 11:30:00 NW

But what I am receiving is this:

CustCount DelCount DriverNo TransTime AirPortCode

-------------------- -------------------- -------- --------- -----------

1 1 042000 04:00:00 NW

2 2 042000 04:00:00 NW

1 1 042000 06:51:00 AA

1 1 042000 08:45:00 NW

1 1 042000 08:52:00 NW

1 1 042000 10:30:00 NW

1 1 042000 10:45:00 NW

2 1 042000 10:45:00 AA

1 1 042000 11:30:00 NW

2 2 042000 11:30:00 NW

The CustCount field should read 1 if there is only one unique AirPortCode during the TransTime with the specific DriverNo, but the query is counting both rows during that transtime.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Sounds to me that the calculation is wrong for your requirement. Perhaps a different windowed aggregate function.

Try this:

select count(distinct Left(ToName,2)) OVER(PARTITION BY TransTime, DriverNo) as CustCount,

ROW_NUMBER() OVER(PARTITION BY Left(ToName,2), TransTime, DriverNo

ORDER BY TransTime) As DelCount

, DriverNo

, Convert(VarChar(8), TransTime, 108) As TransTime

, Left(ToName,2) As AirPortCode

from transactions

That's going to put another issue in the results though, since that will return :

2 1 042000 10:45:00 NW

2 1 042000 10:45:00 AA

instead of

1 1 042000 10:45:00 NW

2 1 042000 10:45:00 AA

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Pulizzo SSC Rookie Points: 48 More actions · Answer 4

This new line produced a syntax error near distinct:

select count(distinct Left(ToName,2)) OVER(PARTITION BY TransTime, DriverNo) as CustCount

I had to write it as:

select distinct count(Left(ToName,2)) OVER(PARTITION BY TransTime, DriverNo) as CustCount

You are correct about the results, this does not give me what I am looking for. Do you think it is even possible to achieve the results I am looking for using this type of function? If this way will not work, what is the best way to go about getting the results I am looking for?

Thanks.

Jeff Moden SSC Guru Points: 1004706 More actions · Answer 5

Jack,

Could you post some data in the form identified in the article in the URL listed in my signature, please? It'll help us give you a correct answer more quickly. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Jeff Moden (6/4/2008)
Jack,
Could you post some data in the form identified in the article in the URL listed in my signature, please? It'll help us give you a correct answer more quickly. Thanks.

Agreed. Getting the specifics on the tables, and the rules, etc... will help.

I think you may need to go back to Row_number(), but split it up into multiple sub-queries, but knowing exactly how the numbers should be derived will be helpful.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Pulizzo SSC Rookie Points: 48 More actions · Answer 7

Thanks for the help, but I have received a working solution from sqlteam.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

At least you got your answer. Just sorry we were slow on the uptake.

Thanks for updating the status!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004706 More actions · Answer 9

Heh... they asked for data and table over there, too! Somebody eventually did it for him, though.

Glad you got an answer, Jack. And, I agree... Dense_Rank is a better solution for this one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)