CODEWITHSUNDEEP
sql-servert-sql
lookslikeanevo
lookslikeanevo

Reputation: 575

row number over partition

cant seem to get row number right, I can get it to count when the template is the same but cant get to keep counting if the template changes

create table #temp
(
question varchar(max)
,rvdt datetime
,name varchar(20)
,cdt datetime
,template varchar(max)
)

insert into #temp
(question ,rvdt ,name ,cdt ,template)
values
('question 1', '11/11/2014 10:56:27 PM', 'Joe', '10/11/2014 11:56:27 PM', 'Tempalte 1'),
('question 2', '11/11/2014 10:56:27 PM', 'Joe', '10/11/2014 11:56:27 PM', 'Tempalte 1'),
('question 3', '11/11/2014 10:56:27 PM', 'Joe', '10/11/2014 11:56:27 PM', 'Tempalte 1'),
('question 11', '11/12/2014 10:56:27 PM', 'Joe', '10/12/2014 11:56:27 PM', 'Tempalte 2'),
('question 22', '11/12/2014 10:56:27 PM', 'Joe', '10/12/2014 11:56:27 PM', 'Tempalte 2'),
('question 32', '11/12/2014 10:56:27 PM', 'Joe', '10/12/2014 11:56:27 PM', 'Tempalte 2'),
('question 1', '11/13/2014 10:56:27 PM', 'Joe', '10/13/2014 11:56:27 PM', 'Tempalte 1'),
('question 2', '11/13/2014 10:56:27 PM', 'Joe', '10/13/2014 11:56:27 PM', 'Tempalte 1'),
('question 3', '11/13/2014 10:56:27 PM', 'Joe', '10/13/2014 11:56:27 PM', 'Tempalte 1'),
('question 11', '11/14/2014 10:56:27 PM', 'Mike', '10/14/2014 11:56:27 PM', 'Tempalte 2'),
('question 22', '11/14/2014 10:56:27 PM', 'Mike', '10/14/2014 11:56:27 PM', 'Tempalte 2'),
('question 33', '11/14/2014 10:56:27 PM', 'Mike', '10/14/2014 11:56:27 PM', 'Tempalte 2')

Select 
ROW_NUMBER() over (partition by name, rvdt order by rvdt )'rv#'
,rvdt
,cdt
,name
,template   
from 
#temp
order by rvdt
drop table #temp

i have tried different combinations with whats in the partitin but i cant seem to get the right one

enter image description here

Upvotes: 2

Views: 150

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Use Dense_Rank() Ranking Function

SELECT Dense_rank()OVER (partition BY name ORDER BY cdt) Rn,
       rvdt,
       cdt,
       name,
       template
FROM   #temp

OUTPUT :

Rn  rvdt                    cdt                     name    template
--  ----------------------- ----------------------- ----    ----------
1   2014-11-11 22:56:27.000 2014-10-11 23:56:27.000 Joe     Tempalte 1
1   2014-11-11 22:56:27.000 2014-10-11 23:56:27.000 Joe     Tempalte 1
1   2014-11-11 22:56:27.000 2014-10-11 23:56:27.000 Joe     Tempalte 1
2   2014-11-12 22:56:27.000 2014-10-12 23:56:27.000 Joe     Tempalte 2
2   2014-11-12 22:56:27.000 2014-10-12 23:56:27.000 Joe     Tempalte 2
2   2014-11-12 22:56:27.000 2014-10-12 23:56:27.000 Joe     Tempalte 2
3   2014-11-13 22:56:27.000 2014-10-13 23:56:27.000 Joe     Tempalte 1
3   2014-11-13 22:56:27.000 2014-10-13 23:56:27.000 Joe     Tempalte 1
3   2014-11-13 22:56:27.000 2014-10-13 23:56:27.000 Joe     Tempalte 1
1   2014-11-14 22:56:27.000 2014-10-14 23:56:27.000 Mike    Tempalte 2
1   2014-11-14 22:56:27.000 2014-10-14 23:56:27.000 Mike    Tempalte 2
1   2014-11-14 22:56:27.000 2014-10-14 23:56:27.000 Mike    Tempalte 2

Upvotes: 3

Related Questions