Ignore duplicate entries in MySQL select using DISTINCT keyword
Posted by Quinn McHenry in 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.
About Quinn McHenry
View more articles by Quinn McHenry
The Conversation
Follow the reactions below and share your own thoughts.
January 28, 2009 at 1:47 pm, ViserExcizer said:
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
June 13, 2009 at 12:40 pm, mistat said:
Works a charm – just what I was looking for – thanks!
July 30, 2009 at 5:51 am, maverick said:
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?
October 27, 2009 at 9:16 am, Anonymous said:
hey maverick i was just wondering if u got the answer to ur question ????
am having the same problem …
thanx
March 08, 2010 at 3:44 pm, Brian said:
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.
September 24, 2010 at 3:15 pm, Britinusag1 said:
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
December 03, 2010 at 9:54 pm, bwy said:
Use
ORDER BY dateofevent DESC
August 14, 2009 at 4:32 pm, chandan said:
some weird distinct in mysql 5.0.1 version
November 21, 2009 at 3:28 pm, indian blogs said:
this is what i was searching
thaks man
February 01, 2010 at 6:37 pm, Frank said:
Thanks, this helped me solve a big problem. You’re the man!
March 31, 2011 at 10:29 am, Hansie said:
pl can anybody help!!
i m stuck with this, select * from table where this!=that and column should be distinct
February 27, 2012 at 1:32 am, bojan said:
@maverick
I found this on MySQL site in comments:
SELECT DISTINCT * FROM table GROUP BY field_name
This should select all the items from the table distinct in regards of field_name…
If you want to select details when more than one field should be unique you just list them in GROUP BY part like field_name1, field_name2
November 15, 2012 at 7:06 pm, Riley said:
Thanks to my father who stated to me on the topic of this blog, this webpage is genuinely amazing.
February 22, 2013 at 2:15 pm, Diseño web said:
I need it. Good info. Thank for sharing.