CODEWITHSUNDEEP
sqlsql-serverdistinct
EmreAltun
EmreAltun

Reputation: 423

How to use DISTINCT when I have multiple column in SQL Server?

I have the following query:

SELECT carBrand, carYear, carModel
FROM Cars;

What I want is to get only different car names.

I wrote this, but it is not what I want:

SELECT DISTINCT carBrand, carYear, carModel
FROM Cars;

How can I solve this problem?

Upvotes: 13

Views: 118083

Answers (4)

Yahia
Yahia

Reputation: 70369

Try thi:

SELECT carBrand, carYear, carModel 
FROM Cars 
GROUP BY carBrand, carYear, carModel;

Upvotes: 19

CodingYoshi
CodingYoshi

Reputation: 26989

I am not sure why the accepted answer has been accepted and certainly do not understand why it has been upvoted but the OP has the following in the question:

I wrote this, but it is not what i want.

select DISTINCT carBrand, carYear, carModel from Cars;

The accepted answer has suggested to use:

SELECT carBrand , carYear ,carModel 
FROM Cars 
GROUP BY carBrand , carYear ,carModel;

which returns the exact same result as the OP's query. Actually the suggestion in the answer (use group by) is not even encouraged to be used for getting distinct results but should be used for aggregation. See this answer for more info.

In addition, SQL Server is smart enough to understand that if there is no aggregation function in the query, then the query is actually asking for distinct so it will use distinct under the hood.

Distinct will do a distinct on the entire row as @MarkByers has indicated in his answer.

For those who want to test the above, here is a script that will create a table with 3 columns and then fill it with data. Both (distinct and group by) will return the same resultset.

CREATE TABLE [dbo].[Cars](
    [carBrand] [varchar](50) NULL,
    [carYear] [int] NULL,
    [carModel] [varchar](50) NULL
)
go;
insert into Cars values('BMW', 2000, '328 i');
insert into Cars values('BMW', 2000, '328 i');
insert into Cars values('BMW', 2000, '328 i');
insert into Cars values('BMW', 2000, '3M');

Both queries will return this result:

carBrand    carYear    carModel
BMW         2000       328 i
BMW         2000       3M

Conclusion

DO NOT use group by if you want distinct records. Use distinct. Use group by when you use aggregate function such as SUM, COUNT etc.

Upvotes: 12

McGarnagle
McGarnagle

Reputation: 102733

It depends what you want. For example if you want 'Toyota Corolla' and 'Toyota Camry', but ignore the year, then you could do this:

SELECT DISTINCT carBrand + ' ' + carModel AS carName
FROM Cars;

Upvotes: 3

Mark Byers
Mark Byers

Reputation: 837966

DISTINCT works on the entire row, not a specific column. If you want to get the unique names, select only that column.

SELECT DISTINCT carBrand FROM Cars

Upvotes: 20

Related Questions