I want to get the row with the nth distinct value in a column, as ordered by another column.

In more real terms, I want the 1000th distinct email_address as ordered by created_at, and the email_address column does not have a unique constraint.

I believe I have done this correctly with the following query:

SELECT `email_address`
    FROM footable
    GROUP BY `email_address`
    ORDER BY `created_at` ASC
    LIMIT 1
    OFFSET 1000

But I am not totally confident that is giving me the right value.

Is the above query correct for this purpose? If not, how should I solve this?

  • Show us an example with 10-20 rows (and how to get, say the 5th "distinct" value). It's not clear otherwise what you mean with "distinct but ordered by another column". Jul 22, 2015 at 13:05
  • As for the query it is not even correct SQL. It's a pity that MySQL allows it and you should not use it for anything. Jul 22, 2015 at 13:06
  • @ypercube how should it be corrected?
    – MHG
    Jul 22, 2015 at 13:09
  • Please explain what you mean with "distinct". If for example the first (ordered by created_at) 500 emails are identical, you would expect the query to return the 1500th row? Jul 22, 2015 at 13:10
  • @ypercube yes exactly, if the remaining 1000 emails are all distinct.
    – MHG
    Jul 22, 2015 at 13:13

2 Answers 2


You need two changes:

  • a major one: Using an non-aggregated column in a GROUP BY query will yield unpredictable result - it's a pity that default setting s in MySQL allow this type of query, and good that it is corrected in 5.7 version.
    The problem with your query is that an email address can appear in many rows - with many different created_at values - and the question is: Which of these different values will MySQL use for the ORDER BY? The answer is any one of the them. It's usually the first it will find but "first" here can mean different things, depending on the execution plans and available indexes. So, the results will not be determinate and may change from one execution to another.
    You can use MIN(created_at) instead for the ORDER BY and the issue is solved.

  • a minor one: OFFSET x means "skip x rows". Replace 1000 with OFFSET 999:

    SELECT email_address
        FROM footable
        GROUP BY email_address
        ORDER BY MIN(created_at) ASC
        LIMIT 1
        OFFSET 999 ;
  • Thanks. Does adding MIN(created_at) for the ORDER BY directly address the major issue you mention or is it a workaround? I'm not clear what the major issue actually is.
    – MHG
    Jul 22, 2015 at 13:21
  • 1
    Yes, it addresses the major issue. I added some more explanation. Jul 22, 2015 at 13:30

Yes you can run those queries:

    FROM footable
    GROUP BY email_address
    ORDER BY created_at ASC
    LIMIT 0,1000;

      DISTINCT email_address
    FROM footable
    ORDER BY created_at ASC
    LIMIT 0,1000;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.