Link to home
Start Free TrialLog in
Avatar of biotec
biotec

asked on

how to use ROW_NUMBER() correctly

I want to pull the most recent appt_date_time but I'm not doing it correctly and hoping I don't have to do a sub select.
select  distinct p.other_id as 'Patient Other ID', p.patient_id as 'Patient ID', p.last_name + ',' + p.first_name as 'Patient Name', ps.date_entered as 'Date', 
ps.service_code as 'Code',  ps.status as Type, ps.description as 'Description', ps.tooth as 'Tooth', ps.surface as 'Surface', ps.fee as 'Fee', ps.provider_id as 'Prov', 
ep.appt_date_time, ROW_NUMBER() OVER (PARTITION BY ep.patient_id ORDER BY ep.appt_date_time DESC)rowno from [dbo].[planned_services] ps--[dbo].[treatment_plans]
inner join patient p on ps.patient_id = p.patient_id
inner join expected_patients ep on ps.patient_id = ep.patient_id
where (ps.completion_date  is null or ps.completion_date = '')
and ep.rowno = 1
order by p.patient_id

Open in new window

Avatar of Aneesh
Aneesh
Flag of Canada image

;WITH C AS (
SELECT  DISTINCT
        p.other_id AS 'Patient Other ID' ,
        p.patient_id AS 'Patient ID' ,
        p.last_name + ',' + p.first_name AS 'Patient Name' ,
        ps.date_entered AS 'Date' ,
        ps.service_code AS 'Code' ,
        ps.status AS Type ,
        ps.description AS 'Description' ,
        ps.tooth AS 'Tooth' ,
        ps.surface AS 'Surface' ,
        ps.fee AS 'Fee' ,
        ps.provider_id AS 'Prov' ,
        ep.appt_date_time ,
        ROW_NUMBER() OVER ( PARTITION BY ep.patient_id ORDER BY ep.appt_date_time DESC ) rowno
FROM    [dbo].[planned_services] ps --[dbo].[treatment_plans]
INNER JOIN patient p ON ps.patient_id = p.patient_id
INNER JOIN expected_patients ep ON ps.patient_id = ep.patient_id
WHERE   ( ps.completion_date IS NULL
          OR ps.completion_date = ''
        )

)
SELECT  * FROM C WHERE rowno = 1
ORDER BY patient_id;
Why DISTINCT. Its not required.
;WITH C AS (
SELECT 
        p.other_id AS 'Patient Other ID' ,
        p.patient_id AS 'Patient ID' ,
        p.last_name + ',' + p.first_name AS 'Patient Name' ,
        ps.date_entered AS 'Date' ,
        ps.service_code AS 'Code' ,
        ps.status AS Type ,
        ps.description AS 'Description' ,
        ps.tooth AS 'Tooth' ,
        ps.surface AS 'Surface' ,
        ps.fee AS 'Fee' ,
        ps.provider_id AS 'Prov' ,
        ep.appt_date_time ,
        ROW_NUMBER() OVER ( PARTITION BY ep.patient_id ORDER BY ep.appt_date_time DESC ) rowno
FROM    [dbo].[planned_services] ps --[dbo].[treatment_plans]
INNER JOIN patient p ON ps.patient_id = p.patient_id
INNER JOIN expected_patients ep ON ps.patient_id = ep.patient_id
WHERE   ( ps.completion_date IS NULL
          OR ps.completion_date = ''
        )

)
SELECT  * FROM C WHERE rowno = 1 
ORDER BY patient_id;

Open in new window

When using analytic functions you are not able to feference them directly in the where clause of the same query. So you do need to use a derived table (or you can use a CTE as an alternative which I do not favour).

Basically you need to do this:

Select *
From (
      Select ..... row_number () over (partition by ... order by ...) as rowno
      From ....
      Where ...
      ) as d
Where d.rowno = 1

The same approach should be used with any function associated with the OVER () clause if you need to subsequently reference it in a where condition.

Please note that "select distinct" slows down queries, so don't use it unless it can be proven to be needed. Even then it isn't often  the best solution to getting unique rows.
By the way there is a logical reason behind this.

Although we start writing sql queries with the word select, the query is NOT exectuted in the written order.

Select ...
From ...
Where

Is actually executed in this order

From ...
Where...
Select...


So, row_number () is calculated AFTER the where clause is finished. That is why we need to result formulated befire we can filter it.
Avatar of biotec
biotec

ASKER

Thanks everyone. I'll try this tomorrow. However, the distinct  gave me the result set I needed, without it the inner join on expected_patients  caused tons of dups (one to many). Basically, it is a table of appointments of which there can be many, I only needed the next most recent. I'm guessing though that the Row_Number() fixes that?
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of biotec

ASKER

Thanks Paul. I need to make sure I'm only getting appointment dates in advance of today's date. Can I just add this where statement to the select you have:

 (SELECT
                        patient_id
                      , appt_date_time
                      , ROW_NUMBER() OVER (PARTITION BY patient_id
                                           ORDER BY appt_date_time DESC) rowno
                  FROM expected_patients
                          where appt_date_time > GetDate()
                 )
Yes you can do that. Because you use an inner join this would reduce the number of rows returned as well.

NB
If a patient had 2 future appointments one a month away and another 3 months from now, then :

If you want the the appointment one month from now you should order the rowno ascending

Else, ordering descendinf will return the appointment 3 months from now.