Solved

CTE Row Number question

Posted on 2011-09-20
5
382 Views
Last Modified: 2013-11-05
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
Comment
Question by:mholbert
  • 3
  • 2
5 Comments
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36568262
I dont see any row number in the result...
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36568269
try not using partition...

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

Author Comment

by:mholbert
ID: 36568272
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
 
LVL 51

Accepted Solution

by:
Huseyin KAHRAMAN earned 125 total points
ID: 36568352
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
 

Author Comment

by:mholbert
ID: 36568417
awesome, gives me precisely what i need.  Apparently i need to better understand the Partition stuff.

thx.  MH
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql disables rename 4 102
Using coldfusion <cfexecute> to restore mysql database 7 64
SSRS: Why is Visual Studio stripping these properties? 2 39
Parse this column 6 34
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question