Retrieving unique rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrieving unique rows

Author

Topic

robson
Starting Member

22 Posts

Posted - 2008-06-03 : 09:09:09

I have the following sql:

SELECT DISTINCT patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date

This brings up duplicate records, my aim is to bring distinct records, now if I take out the other returned fields after patientID
and using the following sql:

SELECT DISTINCT patient.patientID
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID

This bring up distinct results, but I need to retrieve the other fields from the database i.e. patientFirstName and patientLastName

Please can you help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 09:16:56

[code]SELECT patientID,
patientFirstName,
patientLastName,
patientDOB,
patientGender,
completed_date
FROM (
SELECT patient.patientID,
{missing table name}.patientFirstName,
{missing table name}.patientLastName,
{missing table name}.patientDOB,
{missing table name}.patientGender,
{missing table name}.completed_date,
ROW_NUMBER() OVER (PARTITION BY patient.patientID ORDER BY {missing table name}.completed_date DESC) AS RecID
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE ({missing table name}.sub_categoryID = 4 OR patient_record.allocated = 4)
AND {missing table name}.patient_status IN (1, 2, 5)
) AS d
WHERE RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

robson
Starting Member

22 Posts

Posted - 2008-06-03 : 11:02:30

Thank you very much Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources