[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

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

0
Mark Holbert
Asked:
Mark Holbert
  • 3
  • 2
1 Solution
 
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
 
HainKurtSr. 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
 
Mark HolbertAuthor Commented:
awesome, gives me precisely what i need.  Apparently i need to better understand the Partition stuff.

thx.  MH
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now