Mysql Distinct Query returning all records

Question

I have a table user_quotes with the fields quotes_id, quotes_user, quotes_desc, quotes_date, quotes_status, quotes_location. In this quotes_user allows duplication entries. when i execute my query i am trying to avoid duplication entries of quotes_user. so I executed the query like this,

select distinct quotes_user from user_quotes;

This query returning only quotes_user field. How can I retrieve all other records using distinct quotes_user.

I have tried with these following,

select distinct quotes_user, quotes_desc, quotes_date, quotes_status from user_quotes;

Its not avoiding the duplication of quotes_user.

If i use,

select distinct quotes_user, * from user_quotes;

I am getting mysql error,

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM `user_quotes`

Any Ideas how can i fetch all records using select distinct of a single column in a same table. I am storing the email address in the field. the datatype is varchar. Thanks.

Note : Pls dont suggest me other types like group by or other. I need to know how can i retrieve the records by only using distinct. thanks.

Answer 1

It sounds like quotes_user should be a foreign key like user_id to presumably your users table. You could then query user_quotes by user_id returning all the quotes for that user. Your front end could then format all the quotes for each user nicely, it doesn't really sound like a MySql issue.

Answer 2

You say you want to retrieve the other fields, but you haven't specified how SQL is to know which values to retrieve for the other fields, for each distinct value of quotes_user.

To show you want I mean, consider this example:

+-------------+---------------+
| quotes_user | email_address |
+-------------+---------------+
| user1       | email1        |
| user1       | email2        |
| user2       | email3        |
| user2       | email4        |
| user2       | email5        |
| user3       | email6        |
+-------------+---------------+

Now, if you just wanted quotes_user, the output would obviously be:

+-------------+
| quotes_user |
+-------------+
| user1       |
| user2       |
| user3       |
+-------------+

But if you wanted the other fields as well, you'd need to decide whether, for example, to have email1 or email2 for the user1 row.

Perhaps what you want is to concatenate the values of the other fields together. In that case, I would suggest using the GROUP_CONCAT aggregate function with GROUP BY quotes_user.

I'm not sure why you want to avoid using GROUP BY, though. Perhaps if you could explain that, we could help more.

Answer 3

In addition to what has already been said, it should be stressed that the DISTINCT keyword can't give you distinct results for an individual column when you're selecting multiple columns. You will get distinct rows of, in your case, 4 columns. What you're observing is the expected, standard behaviour of DISTINCT.