CTE Row Number question

When i run the following Query, i get the following Results.  My question is, why are the row numbers all the number '1'.  I was hoping for 1,2,3,4,5,6 row numbers.

Query:
With cte_PatientProviders as
(select distinct(lp.desc_30) as Provider,Max(CONVERT(varchar(8), pe.enc_timestamp, 112)) as Enc_Date from patient_encounter pe
inner join lrdc_providers lp on lp.provider_id = pe.rendering_provider_id
where pe.person_id = (select person_id from patients where patients.Chart# = '973903')
group by lp.desc_30)

select * from (select *, row_number() over
(partition by Provider order by enc_date desc) as TEST from cte_PatientProviders) X

Results:
Provider                           Enc_Date
Beadle MD, Beverly      20110919
Beaton MD, J Neal      20050406
Silvoso MD, Gerald R      20000808
Trigg MD, Laura      20110805
Williams MD, Paul      20070409

Mark HolbertAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
because you group by Provider, and then order within group by enc_date, and there is only one record in each group, you get 1 all the time... if you don't group (partition) and use row_number by just ordering the result, you will get 1,2,3,...
0
 
HainKurtSr. System AnalystCommented:
I dont see any row number in the result...
0
 
HainKurtSr. System AnalystCommented:
try not using partition...

select * from (select *, row_number() over (order by enc_date desc) as TEST from cte_PatientProviders) X
0
 
Mark HolbertAuthor Commented:
oops, pasted wrong results.

Provider                           Enc_Date      TEST
Beadle MD, Beverly      20110919      1
Beaton MD, J Neal      20050406      1
Silvoso MD, Gerald R      20000808      1
Trigg MD, Laura      20110805      1
Williams MD, Paul      20070409      1
0
 
Mark HolbertAuthor Commented:
awesome, gives me precisely what i need.  Apparently i need to better understand the Partition stuff.

thx.  MH
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.