Returning Unique Values

[Delboy14]

I am trying to select the most recent records from a table, I also wish to display a value called BTSStep. The problem I am getting is that if there are different BTSStep levels for one patient then the patient is displayed twice. I only want to display the most recent consultation and the BTS Step on that date? Can anyone help me..........Here is my query at present and what it is displaying

SELECT DISTINCT (Consults.PatientIndex) AS PatientIndex, Max(Consults.ConsultDate) AS MaxOfConsultDate, Consults.BTSStep
FROM Consults
GROUP BY Consults.PatientIndex, Consults.BTSStep;

PatientIndex MaxOfConsultDate BTSStep
1 16/11/2000 5
1 07/12/2000 2
2 10/11/2000 2
3 29/11/2000 2
4 14/12/2000 2
5 13/12/2000 2
6 14/12/2000 2
7 13/12/2000 2
8 16/12/2000 2

 

[jes]

Hi There,

try using DISTINCT ROW....it might help,

Jes

 

[tlbroadbent]

Here is one possible solution.

SELECT c.PatientIndex, c.ConsultDate, c.BTSStep
FROM Consults As c INNER JOIN
(SELECT PatientIndex, max(ConsultDate) AS MaxDate
FROM Consults GROUP BY PatientIndex) AS m
ON c.PatientIndex=m.PatientIndex
AND c.ConsultDate=m.MaxDate Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[Delboy14]

thanks alot!!