SQL Query to find record where one of the composite primary keys should be Max against other composite primary key

Question

Following is my table:

PatientID|VisitID|Date|Accident|Diagnosis|Doctor
  1      |    1  |    |        |         | 
  1      |    2  |    |        |         |
  1      |    3  |    |        |         |

Both PatientID and VisitID are composite primary keys. Now I want to show only the last record. In other words where PatientID=1 and VisitID=Maximum.

So what would the query be?

Which DBMS are you using? PostgreSQL? Oracle? DB2? – Jul 02 '12 at 19:23 — , Jul 02 '12 at 19:23

score 0 · Answer 1 · answered Jun 13 '12 at 05:49

0

Assuming that VisitID is sequential:

select top 1 * from yourTable
where PatientID=@patientID
order by VisitID desc

Where: @patientID is a parameter (which, in your example, is 1)

answered Jun 13 '12 at 05:49

Alex R.

4,664
4
30
40

Thanks very much for ur answer.But I tried the following nested query, as objective was to only show the single record having VisitID=3 Select * from tblAccident where PatientID=1 AND VisitNo=(Select Max(VisitNo)from tblAccident where PatientID =1 – M. Suleman Shafique Jun 28 '12 at 10:50
@MuhammadSuleman that's good. you may post that as an answer to your question as well, for future reference. – Alex R. Jun 29 '12 at 01:46

score 0 · Answer 2 · edited Jul 02 '12 at 18:57

0

One of my seniors in the office told the following query, that meets my objective perfectly.

select * 
  from tblAccident 
 where PatientID = 1 
   and VisitNo = ( Select Max(VisitNo) from tblAccident where PatientID = 1 )

edited Jul 02 '12 at 18:57

Ben

51,770
36
127
149

answered Jul 02 '12 at 10:08

M. Suleman Shafique

29
5

SQL Query to find record where one of the composite primary keys should be Max against other composite primary key

2 Answers2