?
Solved

Using row_number in where clause

Posted on 2012-08-14
3
Medium Priority
?
602 Views
Last Modified: 2012-08-14
Hi,

Is it possible to use row number to return only the first row in each partition using this script?

 SELECT 
ROW_NUMBER() OVER(PARTITION BY person.person_id ORDER BY (convert(varchar, convert(datetime, (SELECT max(v) 
   FROM (VALUES (paq_signoff_history.create_timestamp)
				, (sftu_JOE_TX_PLAN_HX.init_tx_plan_signed)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_1)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_2)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_3)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_4)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_5)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_6)) AS value(v))), 101)) desc) AS Orders
,(person.last_name+', '+person.first_name+' '+person.middle_name) Name
,(convert(varchar, convert(datetime, (SELECT max(v) 
   FROM (VALUES (paq_signoff_history.create_timestamp)
				, (sftu_JOE_TX_PLAN_HX.init_tx_plan_signed)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_1)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_2)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_3)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_4)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_5)
				, (sftu_JOE_TX_PLAN_HX.tx_plan_rev_signed_6)) AS value(v))), 101)) as [maxxDate]
 FROM   
 NGProd.dbo.sftu_JOE_TX_PLAN_HX sftu_JOE_TX_PLAN_HX 
 FULL OUTER JOIN NGProd.dbo.person person 
 ON sftu_JOE_TX_PLAN_HX.person_id=person.person_id
 LEFT OUTER JOIN NGProd.dbo.patient_status patient_status 
 ON person.person_id=patient_status.person_id
 LEFT OUTER JOIN NGProd.dbo.patient_documents patient_documents 
 ON person.person_id=patient_documents.person_id
 LEFT OUTER JOIN NGProd.dbo.patient_provider patient_provider 
 ON person.person_id=patient_provider.person_id
 LEFT OUTER JOIN NGProd.dbo.paq_signoff_history paq_signoff_history 
 ON 
 patient_documents.person_id=paq_signoff_history.person_id
 AND 
 patient_documents.enc_id=paq_signoff_history.enc_id
 AND 
 patient_documents.document_id=paq_signoff_history.item_id 
 LEFT OUTER JOIN NGProd.dbo.signatures signatures 
 ON patient_documents.document_id=signatures.doc_id
 LEFT OUTER JOIN NGProd.dbo.patient_encounter patient_encounter 
 ON patient_documents.enc_id=patient_encounter.enc_id 
 AND 
 patient_documents.person_id=patient_encounter.person_id 
 LEFT OUTER JOIN NGProd.dbo.patient_status_mstr patient_status_mstr 
 ON patient_status.patient_status_id=patient_status_mstr.patient_status_id
 WHERE  
 paq_signoff_history.signoff_action='A' AND 
 patient_provider.provider_id='ADB69024-4B7A-4B75-A9A6-BA2695632603' AND 
 patient_status_mstr.description='Active' AND 
 paq_signoff_history.create_timestamp>={ts '2012-03-20 00:31:08'} AND 
 (patient_documents.template_file='bh_master_treatment' OR patient_documents.template_file='sftubh_treatment_plan_review')
 
order by 2, 3 desc

Open in new window

0
Comment
Question by:susnewyork
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38292903
Throw it all in a subquery, then it's a simple WHERE...

SELECT blah, blah, blah
FROM (
   SELECT yada, yada, yada, ROW_NUMBER () OVER (PARTITION BY yada ORDER BY foo) as row_number
   FROM SomeTable ) a
WHERE a.row_number = 1
0
 
LVL 1

Author Closing Comment

by:susnewyork
ID: 38293063
Thanks, much appreciated!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38293096
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

839 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