Solved

CTE Row Number question

Posted on 2011-09-20
5
379 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:HainKurt
ID: 36568262
I dont see any row number in the result...
0
 
LVL 51

Expert Comment

by:HainKurt
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:
HainKurt 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now