Ignore duplicate entries in MySQL select using DISTINCT keyword

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

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

14 Responses to “Ignore duplicate entries in MySQL select using DISTINCT keyword”

  1. 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

    Reply

  2. June 13, 2009 at 12:40 pm, mistat said:

    Works a charm – just what I was looking for – thanks!

    Reply

  3. 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?

    Reply

    • 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

      Reply

      • 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.

        Reply

        • 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

          Reply

          • December 03, 2010 at 9:54 pm, bwy said:

            Use

            ORDER BY dateofevent DESC

  4. August 14, 2009 at 4:32 pm, chandan said:

    some weird distinct in mysql 5.0.1 version :(

    Reply

  5. November 21, 2009 at 3:28 pm, indian blogs said:

    this is what i was searching :) thaks man

    Reply

  6. February 01, 2010 at 6:37 pm, Frank said:

    Thanks, this helped me solve a big problem. You’re the man!

    Reply

  7. 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

    Reply

  8. 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

    Reply

  9. 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.

    Reply

  10. February 22, 2013 at 2:15 pm, DiseƱo web said:

    I need it. Good info. Thank for sharing.

    Reply

Leave a Reply