SQL Apply Distinct One Column

Question

I have a SQL query as below. I want to apply DISTINCT to Name column in this query. Can you help me?

SELECT Id,
       ConflictCheckRequestIndividualId,
       Name,
       Surname,
       it.IndividualType AS IndividualType,
       JobTitle,
       RegistrationNumber,
       Title,
       District,
       Status,
       CreatedBy,
       Created,
       ModifiedBy,
       Modified
  FROM ConflictCheckItoIndividual
  LEFT JOIN @IndividualTypes it
    ON it.IndividualId = ConflictCheckRequestIndividualId
 WHERE ConflictCheckRequestIndividualId IN
       (SELECT Id
          FROM ConflictCheckRequestIndividual
         WHERE ConflictCheckRequestId = @ConflictId
           AND SubStatus = 2)

What result do you expect for a Name that exists several times? (I.e. has several rows - with different data.) — jarlh, May 17 '22 at 13:11
@jarlh only one row is enough, i will show other data by clicking button. I am using Microsoft sql server — Mert POLAT, May 17 '22 at 13:18

score 0 · Answer 1 · answered May 17 '22 at 13:33

Just realize that what your asking for means: You get a random dataset for 1 particular Name, but if that's good enough for you at this moment this code should do:

select * from (
SELECT 
       ROW_NUMBER() over (partition by Name order by Id) [row],
       Id,
       ConflictCheckRequestIndividualId,
       Name,
       Surname,
       it.IndividualType AS IndividualType,
       JobTitle,
       RegistrationNumber,
       Title,
       District,
       Status,
       CreatedBy,
       Created,
       ModifiedBy,
       Modified
  FROM ConflictCheckItoIndividual
  LEFT JOIN @IndividualTypes it
    ON it.IndividualId = ConflictCheckRequestIndividualId
 WHERE ConflictCheckRequestIndividualId IN
       (SELECT Id
          FROM ConflictCheckRequestIndividual
         WHERE ConflictCheckRequestId = @ConflictId
           AND SubStatus = 2)) data
           where [row] = 1

Will you let me know if this works for you?

score 0 · Accepted Answer · answered May 17 '22 at 13:34

Two ways with subtly different results. "GROUP BY X" is another way of saying "Give me one row per X". You will have to apply an aggregation function to each other row so it knows how to squash the rows into 1:

SELECT MAX(Id),
       MAX(ConflictCheckRequestIndividualId),
       Name,
       MAX(Surname),
       MAX(it.IndividualType) AS IndividualType,
       MAX(JobTitle),
       MAX(RegistrationNumber),
       MAX(Title),
       MAX(District),
       MAX(Status),
       MAX(CreatedBy),
       MAX(Created),
       MAX(ModifiedBy),
       MAX(Modified)
  FROM ConflictCheckItoIndividual
  LEFT JOIN @IndividualTypes it
    ON it.IndividualId = ConflictCheckRequestIndividualId
 WHERE ConflictCheckRequestIndividualId IN
       (SELECT Id
          FROM ConflictCheckRequestIndividual
         WHERE ConflictCheckRequestId = @ConflictId
           AND SubStatus = 2)
GROUP BY Name

This might end up with data for each field coming from different rows. If you wanted all data to come from one row, you could do this

;WITH cte AS
(
SELECT Id,
       ConflictCheckRequestIndividualId,
       Name,
       Surname,
       it.IndividualType AS IndividualType,
       JobTitle,
       RegistrationNumber,
       Title,
       District,
       Status,
       CreatedBy,
       Created,
       ModifiedBy,
       Modified,
       ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Modified DESC) as rownum
  FROM ConflictCheckItoIndividual
  LEFT JOIN @IndividualTypes it
    ON it.IndividualId = ConflictCheckRequestIndividualId
 WHERE ConflictCheckRequestIndividualId IN
       (SELECT Id
          FROM ConflictCheckRequestIndividual
         WHERE ConflictCheckRequestId = @ConflictId
           AND SubStatus = 2)
)
SELECT * FROM cte WHERE rownum = 1

This is "Partitioning" the data into one bucket per Name. Within each bucket, its ordering the rows by Modified in descending order. We then only pick out one row from each bucket - the most recently Modified one.

As an aside, given that there is a Name and a Surname field, I would expect to group by that as well

Thank you, I used first query and it worked! – Mert POLAT May 17 '22 at 14:15 — Mert POLAT, May 17 '22 at 14:15

SQL Apply Distinct One Column

2 Answers2