Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
SQL query to only pull most recent value
The query below will pull what I need but pulls all obsvalues and I only want it to pull the most recent. The completeddate is probably what needs to be used in some sort of nested select but I'm not sure how to do this and return values for all rather than just values for one person.
SELECT distinct obsvalue, p.person_id, p.last_name, p.first_name, p.person_nbr, o2.completeddate
FROM order_ o2
INNER JOIN ncsuds_lab_test_desc d ON RTRIM(o2.actText) = RTRIM(d.test_desc) and d.order_ind = 'Y' AND d.selected_diag_id = 42
inner join person p on o2.person_id = p.person_id
WHERE o2.obsvalue IS NOT NULL
AND ISNUMERIC(o2.obsvalue) = 1
and o2.completeddate > '20150101'
order by completeddate desc
SELECT distinct obsvalue, p.person_id, p.last_name, p.first_name, p.person_nbr, o2.completeddate
FROM order_ o2
INNER JOIN ncsuds_lab_test_desc d ON RTRIM(o2.actText) = RTRIM(d.test_desc) and d.order_ind = 'Y' AND d.selected_diag_id = 42
inner join person p on o2.person_id = p.person_id
WHERE o2.obsvalue IS NOT NULL
AND ISNUMERIC(o2.obsvalue) = 1
and o2.completeddate > '20150101'
order by completeddate desc
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<knee-jerk reaction>
Which column defines 'most recent'? If you mean the most recent o2.completedate, then you'll need a MAX.
Check out SQL Server GROUP BY Solutions for some extra info on grouping.
Which column defines 'most recent'? If you mean the most recent o2.completedate, then you'll need a MAX.
SELECT obsvalue, p.person_id, p.last_name, p.first_name, p.person_nbr,
MAX(o2.completeddate) as most_recent_completeddate
FROM order_ o2
INNER JOIN ncsuds_lab_test_desc d ON RTRIM(o2.actText) = RTRIM(d.test_desc) and d.order_ind = 'Y' AND d.selected_diag_id = 42
INNER JOIN person p on o2.person_id = p.person_id
WHERE o2.obsvalue IS NOT NULL
AND ISNUMERIC(o2.obsvalue) = 1
AND o2.completeddate > '20150101'
GROUP BY obsvalue, p.person_id, p.last_name, p.first_name, p.person_nbr
ORDER BY completeddate desc
Check out SQL Server GROUP BY Solutions for some extra info on grouping.
I would try ROW_NUMBER() here:
SELECT * FROM (
SELECT ROW_NUMBER() OVER (p.person_id, p.person_nbr ORDER BY completeddate desc) rowno,
obsvalue, p.person_id, p.last_name, p.first_name, p.person_nbr, o2.completeddate
FROM order_ o2
INNER JOIN ncsuds_lab_test_desc d ON RTRIM(o2.actText) = RTRIM(d.test_desc) and d.order_ind = 'Y' AND d.selected_diag_id = 42
inner join person p on o2.person_id = p.person_id
WHERE o2.obsvalue IS NOT NULL
AND ISNUMERIC(o2.obsvalue) = 1
and o2.completeddate > '20150101') insl
WHERE rowno = 1
This is getting closer but still returns duplicates for many of the persons we're returning the values for. In other words the first two rows in the returned data set are the same person with a value for Sep and another for Nov. It is returning both and I'd only want it to return the entry for Nov.
A small fix:
SELECT * FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY p.person_id, p.person_nbr ORDER BY o2.completeddate desc) rowno,
obsvalue, p.person_id, p.last_name, p.first_name, p.person_nbr, o2.completeddate
FROM order_ o2
INNER JOIN ncsuds_lab_test_desc d ON RTRIM(o2.actText) = RTRIM(d.test_desc) and d.order_ind = 'Y' AND d.selected_diag_id = 42
inner join person p on o2.person_id = p.person_id
WHERE o2.obsvalue IS NOT NULL
AND ISNUMERIC(o2.obsvalue) = 1
and o2.completeddate > '20150101') insl
WHERE rowno = 1
There is a syntax error near the first Order By. Not sure that can be wrapped inside parens like that.
The ROW_NUMBER() requires SQL Server 2005 or newer. The OVER (PARTITION BY ... ORDER BY ...) clause should be OK because it belongs to the ROW_NUMBER().
More info: https://msdn.microsoft.com/en-us/library/ms186734.aspx
If you are using different SQL engine then tell the version please.
BTW, multiplicities on output can also be caused by the INNER JOIN ncsuds_lab_test_desc definition. To join two tables via text description column is not a standard. What is the reason to join ncsuds_lab_test_desc table?
More info: https://msdn.microsoft.com/en-us/library/ms186734.aspx
If you are using different SQL engine then tell the version please.
BTW, multiplicities on output can also be caused by the INNER JOIN ncsuds_lab_test_desc definition. To join two tables via text description column is not a standard. What is the reason to join ncsuds_lab_test_desc table?
>This is getting closer but still returns duplicates for many of the persons we're returning the values for.
Please show us some sample data, both your data source and what the above queries are returning.
Please show us some sample data, both your data source and what the above queries are returning.
ROW_NUMBER() generates line numbers for your data and the OVER() defines rules for such numbering, namely grouping (partitioning) and the order of the data.
To get the latest result for each person we used p.person_id for grouping (p.person_nbr is not necessary probably) and ordered the data by completeddate DESC.
Thanks to the descending order the latest result has ROW_NUMBER() = 1 for each person.
I agree the ROW_NUMBER() is a little bit unclear at the first view.
BTW, this part of your query is still unclear to me:
INNER JOIN ncsuds_lab_test_desc d ON RTRIM(o2.actText) = RTRIM(d.test_desc) and d.order_ind = 'Y' AND d.selected_diag_id = 42
but that's not important maybe.
To get the latest result for each person we used p.person_id for grouping (p.person_nbr is not necessary probably) and ordered the data by completeddate DESC.
Thanks to the descending order the latest result has ROW_NUMBER() = 1 for each person.
I agree the ROW_NUMBER() is a little bit unclear at the first view.
BTW, this part of your query is still unclear to me:
INNER JOIN ncsuds_lab_test_desc d ON RTRIM(o2.actText) = RTRIM(d.test_desc) and d.order_ind = 'Y' AND d.selected_diag_id = 42
but that's not important maybe.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial