Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Query to delete patients last visit.

Posted on 2013-06-09
Medium Priority
224 Views
I have a table that contains a list of patient id's and visit years .  For example:

``````PatientID  VisitYear
A1              1
A1              2
A1              2
A1              3
A1              3
A2              1
A2              2
A2              2
A3              1
``````

I need Query that will delete ALL the entries for the patients last year.  So the above table would look like

``````PatientID  VisitYear
A1              1
A1              2
A1              2
A2              1
``````
0
Question by:soozh
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 22

Expert Comment

ID: 39233145
``````DECLARE @t TABLE (PatientID varchar(10), VisitYear int)

INSERT @t
SELECT 'A1', 1
UNION ALL SELECT 'A1', 2
UNION ALL SELECT 'A1', 2
UNION ALL SELECT 'A1', 3
UNION ALL SELECT 'A1', 3
UNION ALL SELECT 'A2', 1
UNION ALL SELECT 'A2', 2
UNION ALL SELECT 'A2', 2
UNION ALL SELECT 'A3', 1

DELETE T
FROM
(SELECT  rn=RANK() OVER (PARTITION BY PatientID ORDER BY VisitYear DESC)
FROM @t
) T
WHERE rn=1

SELECT * FROM @t
``````
0

LVL 41

Assisted Solution

Sharath earned 1000 total points
ID: 39233880
Another method.
``````delete t1
from YourTable t1
join (select PatientID,max(VisitYear) VisitYear from YourTable group by PatientID) t2
on t1.PatientID = t1.PatientID and t1.VisitYear = t2.VisitYear
``````
0

LVL 32

Accepted Solution

awking00 earned 1000 total points
ID: 39237783
delete from yourtable as a
where exists
(select b.patientid, max(b.visityear)
from yourtable as b
group by b.patientid
having b.patientid = a.patientid
and max(b.visityear) = a.visityear);
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
###### Suggested Courses
Course of the Month8 days, 16 hours left to enroll