How to create fast database queries

Selecting non-unique rows

with one comment

Sometimes we need to select all rows for a table that have duplicate values in some of the columns. Like, we want to select all user comments for all posts commented by more than one user.

If there are two or more comments for a post, we select all comments for this post; if there is only one comment, we select none.

Let's create the sample tables to illustrate our needs:

Table creation details

Written by Quassnoi

March 22nd, 2009 at 11:00 pm

Posted in MySQL

One Response to 'Selecting non-unique rows'

Subscribe to comments with RSS

  1. I really like your articles and hope that you will continue this blog in a future. Thank you for sharing your knowledge.
    One question on the second version of the query.
    I assume that there are no sense to use a derrived query
    (… SELECT DISTINCT value FROM t_dup… ) because we anyway join the results with t_dup and after that use the subquery in WHERE clause to all rows in t_dup. I just removed the derrived query and got the same results. Am I right or may be I miss something?

    The corrected variant of the second query:

    SELECT SUM(id)
    FROM (
    SELECT id, value
    FROM t_dup dpd
    SELECT 1
    FROM t_dup dpi
    WHERE dpi.value = dpd.value
    LIMIT 1, 1
    ) q


    11 Jan 17 at 14:18

Leave a Reply