MYSQL choosing value used of column with different values when Grouping

Question

Sample mysql table:

carnum,cartype,carname
1,sportscar,mustang
2,sportscar,mustang GT
3,sportscar,mustang GT

When I run this query:

SELECT carname, cartype
FROM cars
GROUP BY cartype

I get:

mustang,sportscar

What I want is:

mustang GT,sportscar

Is there a way to make the group use the most recent carname (mustang GT) rather than the first one (mustang)? I tried sorting in various ways but had no luck.

Thanks very much.

This is sort of a fruitless effort. MySQL does not guarantee the order of records. This is not really how `GROUP BY` is intended to be used. — Mike Brant, Jun 02 '14 at 18:16
possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Marcus Adams, Jun 02 '14 at 18:26

score 0 · Answer 1 · answered Jun 02 '14 at 18:49

0

try with this one

select carname, cartype from cars 
where carnum in(
select max(carnum) from cars group by cartype
);

answered Jun 02 '14 at 18:49

neiha

171
5

score -1 · Answer 2 · answered Jun 02 '14 at 18:19

-1

You may want to try:

Select Distinct carname, cartype FROM cars

or

Select top 1 carname, cartype FROM cars Order by carname

This will give you what you want in this case, but depending on your final goal, may not be ideal. If you provide more details, we may be able to help more.

answered Jun 02 '14 at 18:19

David Manheim

2,553
2
27
42

If there is a problem with an answer, comments (not just a downvote) would be appreciated. – David Manheim Jun 02 '14 at 18:42

MYSQL choosing value used of column with different values when Grouping

2 Answers2