CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Billa
Billa

Reputation: 5266

Fetch DISTINCT Record with JOIN

I want to take a distinct patient records and show it in a grid

Patient Table

PatientId, FirstName, LastName, City

Booking Table

BookingId, PatientId, CategoryId, BookingDate

Running below query gives duplicate patient records. This is happening because I have 3 bookings for same patient for different date. This query makes join and brings 3 patient records instead of 1 patient record. 

 SELECT  DISTINCT PAT.PatientId  
 ,PAT.FirstName
 ,PAT.LastName      

 ,ROW_NUMBER() OVER (ORDER BY PAT.PatientId DESC) AS RowNumber 
 INTO #itemSearch  
 FROM  dbo.Patient AS PAT  

 INNER JOIN dbo.Booking AS B  
 ON PAT.PatientId = B.PatientId WHERE  B.CategoryId = 1

If I remove this line I get only 1. But I need this temp table for other paging process.

ROW_NUMBER() OVER (ORDER BY PAT.PatientId DESC) AS RowNumber INTO #itemSearch

How can I get only 1 patient even though they have 3 bookings?

Upvotes: 2

Views: 165

Answers (1)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Use GROUP BY clause

 SELECT PAT.PatientId  
        ,PAT.FirstName
        ,PAT.LastName      
        ,ROW_NUMBER() OVER (ORDER BY PAT.PatientId DESC) AS RowNumber 
 INTO #itemSearch  
 FROM  dbo.Patient AS PAT  
   INNER JOIN dbo.Booking AS B  
     ON PAT.PatientId = B.PatientId 
 WHERE  B.CategoryId = 1
 GROUP BY PAT.PatientId, PAT.FirstName, PAT.LastName

OR use DISTINCT with DENSE_RANK() ranking function

 SELECT DISTINCT PAT.PatientId  
        ,PAT.FirstName
        ,PAT.LastName      
        ,DENSE_RANK() OVER (ORDER BY PAT.PatientId DESC) AS RowNumber 
 INTO #itemSearch  
 FROM  dbo.Patient AS PAT  
   INNER JOIN dbo.Booking AS B  
     ON PAT.PatientId = B.PatientId 
 WHERE  B.CategoryId = 1

Upvotes: 2

Related Questions