Geek Answers Handbook

SELECT DISTINCT, but return all data

So, I have a table full of cars:

Let's say that there are 4 dodge viper, there will be 4 entries in the database, they are all identical, except for carID.

carID | carname | carmodel | colour | reg -------------------------------------------------- 1 | viper | dodge | red | 123 2 | viper | dodge | red | 124 3 | viper | dodge | red | 125 4 | viper | dodge | red | 126 5 | R8 | audi | blue | 127 6 | R8 | audi | blue | 128

When a user searches for cars, I want to display only one evasion viper. However, I want to get all the information from this line and every other individual car.

So I want:

  carID | carname | carmodel | colour | reg -------------------------------------------------- 1 | viper | dodge | red | 123 5 | R8 | audi | blue | 127

If I do this:

 SELECT DISTINCT * FROM cars

he discards all entries.

 SELECT DISTINCT carname FROM cars

Only one of them selects, but I have only the name of the car.

Is there such a thing:

 SELECT * FROM cars ORDER BY DISTINCT carname

Or something similar?

+4
source share
3 answers

If you want to return its carId , you can use the following, which will return min(carid) for each identical carname , etc.:

 select min(carId) as carId, carname, carmodel, colour from cars group by carname, carmodel, colour

See SQL Fiddle with Demo

You will see that I put carId in an aggregate function to make sure MySQL will always return the expected value for the carId column. If you are not GROUP BY or you are aggregating items in a SELECT list, you may return unexpected results. (see MySQL Extensions to GROUP BY )

In MySQL Docs:

MySQL expands the use of GROUP BY so that the selection list can refer to non-aggregated columns not named in the GROUP BY clause .... You can use this function to improve performance by avoiding unnecessary sorting and grouping of columns. However, this is useful primarily when all the values ​​in each non-aggregated column not named in GROUP BY are the same for each group. The server can select any value from each group, therefore, if they do not match, the selected values ​​are undefined. Moreover, the selection of values ​​from each group cannot depend on the addition of an ORDER BY clause. The result set is sorted after the values ​​have been selected, and ORDER BY does not affect the values ​​that the server selects.

+3
source

I do not understand, do you mean the grouping?

 SELECT MAX(CarId) as carId, carname, carmodel, colour FROM cars GROUP BY carname, carmodel, colour;
+3
source

Try the following:

 SELECT DISTINCT carname, otherCol1, otherCol2... FROM cars

SQLFiddle Here

As you edited the question and want to display your car_id, the solution above does not fit. Here you are:

 SELECT MIN(carId) AS carId, carname, carmodel, colour FROM cars GROUP BY carname, carmodel, colour

MIN or MAX SUM No matter what you use, it's just a projection ... perception you can tell

+1
source

More articles:


All Articles