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.

  • http://tribux.net ViserExcizer

    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

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

  • maverick

    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?

    • Anonymous

      hey maverick i was just wondering if u got the answer to ur question ????
      am having the same problem …
      thanx

      • http://www.vqcdesigns.com/ Brian

        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

          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

            Use

            ORDER BY dateofevent DESC

  • http://www.mac.co.in/ chandan

    some weird distinct in mysql 5.0.1 version :(

  • http://www.iblogs.in/ indian blogs

    this is what i was searching :) thaks man

  • Frank

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

  • Hansie

    pl can anybody help!!
    i m stuck with this, select * from table where this!=that and column should be distinct

  • bojan

    @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

  • Riley

    Thanks to my father who stated to me on the topic of this blog, this webpage is genuinely amazing.

  • Diseño web

    I need it. Good info. Thank for sharing.