Solved

CTE Row Number question

Posted on 2011-09-20
5
374 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
Comment Utility
I dont see any row number in the result...
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
awesome, gives me precisely what i need.  Apparently i need to better understand the Partition stuff.

thx.  MH
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 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

12 Experts available now in Live!

Get 1:1 Help Now