Return the most recent row
-
Hello!
I'm new to SQL and i've searched everywhere for the answer to this question but I can't seem to find the anwer anywhere. My question is.....
If there are multiple rows for a specific filed, is there a way in SQL to only return the last row based on that specific field.
For example, I wrote a query that returns a list of patients who have a specific lab type. If a patient has had several labs taken, they show up in the result set several times (for each lab date). I only want to return the row with the most recent lab date.
Here is my query........
SELECT id, resulted_test_desc, result_value, units, update_date
FROM dbo.lab_result LEFT OUTER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN
dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
WHERE resulted_test_desc = 'Hemoglobin'
Any help would be greatly appreciated.
Thanks!
Amedeo
-
One way of doing this is as follows:
SELECT id, resulted_test_desc, result_value, units, update_date
FROM dbo.lab_result LEFT OUTER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN
dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
WHERE resulted_test_desc = 'Hemoglobin'
and lab_result.<date_time_field> =
(select max (t2.<date_time_field>
from lab_result t2
where t2.<patient_field> = lab_result.<patient_field>
and t2.resulted_test_desc = lab_result.resulted_test_desc)
The sub select gets the most recent date_time_field for the same patient for the same test.
J
-
I didn't do exactly what you posted but your suggestion led me to figuring it out. I used the MAX() function in the SELECT statement and used a GROUP BY for all of the fields listed in the SELECT. Thanks for all of your help. I really appreciate it.
Amedeo
-
If you're using SQL Server 2005 (I assume you're not, because you're posting on the SQL Server 7/2000 forum, but just in case
) there's another solution using ROW_NUMBER() and a subquery.SELECT X.id, X.resulted_test_desc, X.result_value, X.units, X.update_date
FROM (
SELECT id, resulted_test_desc, result_value, units, update_date, ROW_NUMBER() OVER (PARTITION BY id ORDER BY update_date DESC) AS RowNumber
FROM dbo.lab_result LEFT OUTER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN
dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
WHERE resulted_test_desc = 'Hemoglobin' ) AS X
WHERE X.RowNumber = 1
The subquery will return something like this:
id .... other fields here ..... update_date RowNumber
1 08/19/2006 1
1 05/01/2006 2
1 01/08/2006 3
2 07/07/2007 1
3 10/25/2006 1
3 08/16/2006 2
And you only have to pick those records whose RowNumber is 1.
I hope this helps.
-
The easiest way to get the one most recent record from some group of records is simply SELECT TOP 1
SELECT
TOP 1 id, resulted_test_desc, result_value, units, update_date
FROM dbo.lab_result
LEFT JOIN dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart
LEFT JOIN dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
WHERE resulted_test_desc = 'Hemoglobin'
ORDER BY update_date DESC
If you want to get the most recent record in each group simultaneously, and you don't have SQL 2005 so you can't use ROW_NUMBER(), use a temp table with a unique index and add the IGNORE_DUP_KEY attribute to exclude the records you don't want. On small tables you can use a self-join to a grouped subquery with MAX(update_date), but for larger tables the IGNORE_DUP_KEY method is much faster.
-- Create a temp table with no data
SELECT id, resulted_test_desc, result_value, units, update_date
INTO #results
FROM dbo.lab_result
LEFT JOIN dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart
LEFT JOIN dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
WHERE 1=0
-- Add a unique index
CREATE UNIQUE INDEX IX_results ON #results (resulted_test_desc) WITH IGNORE_DUP_KEY
-- Add the data with ORDER BY, only the first record for each group is kept
SELECT id, resulted_test_desc, result_value, units, update_date
INTO #results
FROM dbo.lab_result
LEFT JOIN dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart
LEFT JOIN dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key
ORDER BY update_date DESC
SELECT * FROM #results
DROP TABLE #results
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply