Ignore duplicate entries in MySQL select using DISTINCT keyword
Contributed by qmchenry 
Tagged: MySQL Sometimes every occurance of a value which may be duplicated multiple times in a result set is not needed. For example, if making a pulldown menu list of options, each option should be seen only once. The DISTINCT keyword in a select statement eliminates duplication in the result set.
The column party from the presidents sample table has many repeats in it. To select a list of the parties from the table, use:
SELECT DISTINCT party FROM presidents;
This returns a result set that looks like:
+-----------------------+
| party |
+-----------------------+
| no party |
| Federalist |
| Democratic-Republican |
| Democratic |
| Whig |
| Republican |
+-----------------------+
Using the DISTINCT keyword on queries with multiple columns will return unique combinations of values on all of the columns. For example, the query
SELECT DISTINCT givenname, party FROM presidents;
returns 36 out of a possible 43 rows. Each row is a unique combination of first name and political party.
You may also like -
SQL Server: How To Find Duplicate Records
Get MySQL date in RFC-822 format for RSS feeds
Outlook: Rules Create Multiple Duplicate Message
MySQL: How to get row number order
Outlook: How To Delete Duplicated Contacts
Save MySQL query results into a text or CSV file
SQL Server 2005: Locating Stored Procedures that Contain a Keyword
Solve: Can't connect to local MySQL server through socket /tmp/mysql.sock
ViserExcizer said on January 28, 2009
I;ve experimented with both GROUP BY and DISTINCT fucntions in select statements, for some reason, distinct doesn’t behave the way it should, for me when the array is fetched in a loop
mistat said on June 13, 2009
Works a charm – just what I was looking for – thanks!
maverick said on July 30, 2009
How do you select query with multiple columns unique distinct values from a single column?
statement:
select givenname, party from presidents;
I want only the givenname to be distinct and not the party?
chandan said on August 14, 2009
some weird distinct in mysql 5.0.1 version :(
Anonymous said on October 27, 2009
hey maverick i was just wondering if u got the answer to ur question ????
am having the same problem …
thanx
indian blogs said on November 21, 2009
this is what i was searching :) thaks man
Frank said on February 1, 2010
Thanks, this helped me solve a big problem. You’re the man!
Brian said on March 8, 2010
You can’t do that with DISTINCT.
You would do that using GROUP BY on your query. MySQL will maintain association of the row data, so you can’t for example select distinct givenname and return all of the party results at the same time because one might return 50 results and the other only 2 or something of that nature.
You may be looking for something more like:
SELECT givenname, party FROM presidents GROUP BY givenname
This will return a resultset with each row having a distinct givenname while also returning the associated party for that person.
Britinusag1 said on September 24, 2010
How about selecting the most recent of all the duplicates?
ie. If table has columns table.key,table.userid,table.dateofevent,table.event
Then how to select the most recent event for each userid?
TIA.
Paul
bwy said on December 3, 2010
Use
ORDER BY dateofevent DESC
Hansie said on March 31, 2011
pl can anybody help!!
i m stuck with this, select * from table where this!=that and column should be distinct