1

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". – ypercubeᵀᴹ Jul 22 '15 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. – ypercubeᵀᴹ Jul 22 '15 at 13:06
  • @ypercube how should it be corrected? – Hugh Grigg 葛修远 Jul 22 '15 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? – ypercubeᵀᴹ Jul 22 '15 at 13:10
  • @ypercube yes exactly, if the remaining 1000 emails are all distinct. – Hugh Grigg 葛修远 Jul 22 '15 at 13:13
4

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 ;
    
| improve this answer | |
  • 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. – Hugh Grigg 葛修远 Jul 22 '15 at 13:21
  • 1
    Yes, it addresses the major issue. I added some more explanation. – ypercubeᵀᴹ Jul 22 '15 at 13:30
-1

Yes you can run those queries:

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

    SELECT 
      DISTINCT email_address
    FROM footable
    ORDER BY created_at ASC
    LIMIT 0,1000;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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