Using row_number in where clause

Posted on 2012-08-14
Last Modified: 2012-08-14

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

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]
 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 
 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 
 LEFT OUTER JOIN NGProd.dbo.patient_status_mstr patient_status_mstr 
 ON patient_status.patient_status_id=patient_status_mstr.patient_status_id
 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

Question by:susnewyork
    LVL 65

    Accepted Solution

    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
    LVL 1

    Author Closing Comment

    Thanks, much appreciated!
    LVL 65

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    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 …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now