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.

  • SQL Select Permissions
  • Can I “join” across datasources in SSRS?
  • Retrieving multiple xml child node values
  • How do I Sort a varchar with numbers and letters without a specific format?
  • Using sys.fn_IsBitSetInBitmask in SQL Azure
  • java.sql.BatchUpdateException: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value
  • I wrote this, but it is not what i want.

    select DISTINCT carBrand, carYear, carModel from Cars;
    

    how can I solve this problem?

    4 Solutions collect form web for “how to use DISTINCT when I have multiple column in sql server”

    try

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

    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
    

    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;
    

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.