Individual Records - 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)

Individual Records

Author

Topic

robson
Starting Member

22 Posts

Posted - 2008-07-31 : 11:36:04

I have the following Stored Procedure which brings up duplicate redcords, how would I change it select Unique records. Thank you.

CREATE PROCEDURE <SP_NAME>
@categoryID int,
@row_number int,
@sub_categoryID int
AS
SELECT TOP 10 *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY patient.patientID ORDER BY patientLastName, patientFirstName) AS RowNumber,
patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, allocated
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE (allocated = case when @categoryID<>12 then @sub_categoryID else allocated end)
AND (risk = 6) AND (completed_date = '' OR completed_date IS NULL)
GROUP BY patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, allocated) _myResults
WHERE RowNumber > @row_number

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 12:56:53

Without seeing your data and your expected o/p its very difficult to understand wht you mean by duplicates. Can you illustrate with some sample data what you are expecting for an output?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources