I'm having a hard time figuring out how to output what I want. I want to remove duplicates from one column only, while allowing duplicates in other columns. I'm inner joining three tables (a,b,c) using primary and foreign keys. Here's what I have, will someone explain how to modify this query to output records having a unique value in COLU2, while still allowing duplicates in the surrounding columns (COLU1, COLU3)?
QUERY:
DECLARE @Email NVARCHAR(50)
SET @Email = '[email protected]'
SELECT a.nameF, a.nameL, a.email b.TestName, c.testScore FROM a
INNER JOIN b
ON a.a_primaryKey=b.a_foreignKey
INNER JOIN c
ON b.b_primaryKey=c.b_foreignKey
WHERE a.email = '%' + @Email + '%'
CURRENT OUTPUT:
COLU1 | COLU2 | COLU3
------------------------------
[email protected] | test1 | 80
[email protected] | test1 | 90
[email protected] | test2 | 83
[email protected] | test3 | 89
WISHLIST OUTPUT:
COLU1 | COLU2 | COLU3
------------------------------
[email protected] | test1 | 80
[email protected] | test2 | 83
[email protected] | test3 | 89
I've tried DISTINCT after SELECT along with GROUP BY, but I couldn't get it to work. Thanks all.
I want to remove duplicates from one column only, while allowing duplicates in other columns.
Use aggregation: all columns whose distinct values you want to retain go to the GROUP BY clause, and you can use an aggregate function on the other column:
SELECT a.nameF, a.nameL, a.email b.TestName, MIN(c.testScore) score
FROM a
INNER JOIN b ON a.a_primaryKey=b.a_foreignKey
INNER JOIN c ON b.b_primaryKey=c.b_foreignKey
WHERE a.email = '%' + @Email + '%'
GROUP BY a.nameF, a.nameL, a.email b.TestName