12

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?

informatik01
  • 16,038
  • 10
  • 74
  • 104
EmreAltun
  • 393
  • 6
  • 9
  • 19

4 Answers4

18

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
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
18

Try thi:

SELECT carBrand, carYear, carModel 
FROM Cars 
GROUP BY carBrand, carYear, carModel;
informatik01
  • 16,038
  • 10
  • 74
  • 104
Yahia
  • 69,653
  • 9
  • 115
  • 144
11

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.

CodingYoshi
  • 25,467
  • 4
  • 62
  • 64
3

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;
McGarnagle
  • 101,349
  • 31
  • 229
  • 260
  • Not trying to nitpick or be a jerk as SQL is not what I spend nearly as much time with, but I certainly always felt that it is super simple to ONLY get `DISTINCT` on a single column , however I stumbled across this page as I was only getting 20 records from `SELECT distinct id from tablename` and yet 1000's of records from `SELECT distinct id, pid from tablename` – Tom Stickel Jun 09 '16 at 21:49
  • 2
    `SELECT distinct id, pid from table ` == `select distinct(id,pid) from table ` that distinct keyword works as combination of all the column names. it has secret brackets that wrapss all the columns . may confuse people – bh_earth0 Nov 30 '17 at 15:36