want to show records with duplicate criteria - any easier way to do this?

sfullman

OK I already know this will work:

/* list only the filesize attribute in a view */
CREATE OR REPLACE VIEW _v_duplicate_file_sizes AS
SELECT FileSize FROM items GROUP BY FileSize HAVING COUNT(*) > 1;
/* now show all items that have two or more records with the same file size */
SELECT i.* FROM items i JOIN _v_duplicate_file_sizes d ON i.FileSize=d.FileSize;

BUT! Any way to do the same thing in one query? Just curious. Thanks

bradgrafelman

Can you tell us a little more about what information you need from the result, e.g. what's the bigger picture going on here? (Also... which DBMS? I'll assume MySQL as usual.)

For example, you might be able to get away with just the first SELECT and some use of MySQL's GROUP_CONCAT(). Or, you might be able to do something like:

SELECT *
FROM items
WHERE
  FileSize IN
  (
    SELECT FileSize
    FROM items
    GROUP BY FileSize
    HAVING COUNT(*) > 1
  )