row_number function

Question

Post reply

row_number function

sqlislife

Ten Centuries

Points: 1251
More actions
January 13, 2010 at 5:47 pm

#215421

Good day professionals,
Here's a thinker, I have the following table below
create table test
(
id int,
name varchar(20)
)
insert into test (id, name)
select 1, 'apple' union all
select 1, 'apple' union all
select 1, 'apple' union all
select 2, 'orange' union all
select 2, 'orange' union all
select 2, 'orange' union all
select 2, 'orange' union all
select 3, 'banana' union all
select 3, 'banana' union all
select 3, 'banana' union all
select 3, 'banana' union all
select 3, 'banana'
I get the number of occurences as well as a numbering of each fruit with the code below
select ID, name,
row_number() over (partition by id order by name asc) as Numbering,
count(*) over (partition by id) as NumberOfFruits
from test
Here's the thinker,
How do I tweak the code to be able to replace my "nummbering" column with the numbering of the total number of fruits instead. For example since there are a total of three fruits, how do I re-arrange the code to get a desired result like the one in the table below instead of the table above.
Table with desired result
create table test1
(
id int,
name varchar(20),
Numbering int,
NumberOfFruits int
)
insert into test1 (id, name, numbering, numberoffruits)
select 1, 'apple', 1, 3 union all
select 1, 'apple', 1, 3 union all
select 1, 'apple', 1, 3 union all
select 2, 'orange', 2, 4 union all
select 2, 'orange', 2, 4 union all
select 2, 'orange', 2, 4 union all
select 2, 'orange', 2, 4 union all
select 3, 'banana', 3, 5 union all
select 3, 'banana', 3, 5 union all
select 3, 'banana', 3, 5 union all
select 3, 'banana', 3, 5 union all
select 3, 'banana', 3, 5
select * from test1
Thanks

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

SELECT t1.ID, t1.Name, t1.ID AS Numbering,

(SELECT COUNT(*) AS NumberOfFruits FROM Test t2 WHERE t2.ID = t1.ID) AS NumberOfFruits

FROM dbo.Test t1

--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)

sqlislife Ten Centuries Points: 1251 More actions · Answer 2

Thanks for your response Jeff. That solution worked well with that ID. What if the id column had like 5 numbers instead of 1 number. Like say inserting the following into the test table

insert into test1 (id, [name])

select 1234, 'apple' union all

select 2345, 'orange' union all

select 3456, 'banana' union all

select 4567, 'guava' union all

select 4567, 'guava'

how do I still get my "numbering" and "numberoffruits" columns to still be the same single digits..i.e. 1,1,1, 2,2,2,2, 3,3,3,3 e.t.c

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Check out the dense_rank function is BOL to achieve that

Clear Sky SQL
My Blog[/url]

sqlislife Ten Centuries Points: 1251 More actions · Answer 4

Thanks but I tried the dense_rank and it didn't work for me. Great piece of information learned though.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

sqlislife (1/14/2010)
Thanks but I tried the dense_rank and it didn't work for me. Great piece of information learned though.

Presumably this?

select ID, name,

dense_rank() over (order by id asc) as Numbering,

count(*) over (partition by id) as NumberOfFruits

from test

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

sqlislife Ten Centuries Points: 1251 More actions · Answer 6

That worked like a charm. The reason it didn't work for me previously was that I used the dense_rank () over (partition by id). I have learned a lot from you guys. Thanks a bunch.