CODEWITHSUNDEEP
sqlsql-serverselectsubquery
Raffaell
Raffaell

Reputation: 5

Select multiple columns but distinct only one in SQL?

Lets say I have a table called TABLE with the columns col1, col2, col3 and col4

I want to select col1, col2 and col3 but distinct col2 values from the others, but I can't do it.

I tried something like this:

SELECT DISTINCT "col1", "col2", "col3" FROM [Table] WHERE col1 = Values

But the output brings me more than one record of col 2 with the same value. I know that is because the distinct filtered all the columns that i specified, but i don't know how to get all the columns and filter only the values of col2.

Is it possible to SELECT more than 1 column but filter only one of them with SELECT DISTINCT ?

Upvotes: 0

Views: 904

Answers (1)

John Ink
John Ink

Reputation: 526

As you said, distinct just limits the full set of columns to eliminate duplicates. Instead, I'd just use an aggregate function with a GROUP BY statement.

SELECT MAX(col1) AS col1, col2, 
MAX(col3) AS col3
FROM tbl
GROUP BY col2

That will take the top value alphanumerically from the supplied columns. Or, to list all values separated by commas:

SELECT STRING_AGG(col1,',') AS col1, col2, 
STRING_AGG(col3,',') AS col3
FROM tbl
GROUP BY col2

Upvotes: 0

Related Questions