In SQL, the same two people must be one person-CodePudding

I wrote a sql query but as you noticed there are two identical records with the same name and id number. I need to show them all somehow without using Distinct. The middleware I'm using fills in values based on UserTableID. If we use DISTINCT, the query will not work while UserTableID is present. Can you help me?

select UserTableID as [ID], Ad_Soyad AS [NAME SURNAME], Kimlik_No AS [IDENTIFICATION NUMBER]
from SA_OtelRezervasyon

ID   NAME SURNAME          IDENTIFICATION NUMBER 
1     Ali AKYÜZ              11111111111
2     Osman                  22222222222
3     Ali AKYÜZ              11111111111
4     Mustafa Batuhan        33333333333

CodePudding user response：

Or just using simple old SQL:

select min(UserTableID) as [ID], Ad_Soyad AS [NAME SURNAME], Kimlik_No AS [IDENTIFICATION NUMBER]
from SA_OtelRezervasyon
group by Ad_Soyad, Kimlik_No

CodePudding user response：

I would generally opt first, to see if I could correct the error in the data. If the record should not be duplicated, I would see if I could remove either ID 1 or ID 3. I would first see if JOIN data was associated with both IDs and UPDATE any Foreign Key references to a single record.

Then, I would save the steps needed, so I could look out for new duplicates, and try to have the application team(s) if applicable help determine how the duplicates are being created in the first place.

If this is done, your query would not need to change.

CodePudding user response：

You can use ROW_NUMBER function do eliminate duplicate rows.

WITH CTE_OtelRezervasyon AS
(
   SELECT UserTableID AS [ID]
   , Ad_Soyad AS [NAME SURNAME]
   , Kimlik_No AS [IDENTIFICATION NUMBER]
   , ROW_NUMBER() OVER (PARTITION BY Ad_Soyad, Kimlik_No ORDER by UserTableID) AS RN
   FROM SA_OtelRezervasyon
)
SELECT [ID], [NAME SURNAME],  [IDENTIFICATION NUMBER]
FROM CTE_OtelRezervasyon 
WHERE RN = 1;

CodePudding user response：

Your User table ID has Duplicates in it and you should update your data there because when you have two id numbers for one person you will definitely get it twice in your table.

CodePudding user response：

Try this (using ROW_NUMBER):

WITH row_table AS (
       SELECT ROW_NUMBER() OVER(PARTITION BY ut.ID_Num ORDER BY ut.ID) AS new_row_number
              ,ut.UserTableID
              ,ut.Ad_Soyad
              ,ut.Kimlik_No
       FROM dbo.UserTable ut
       )
select * 
FROM row_table rt where rt.new_row_number = 1 order by rt.ID