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
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;
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.
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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()
)
(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.
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.
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;