It is hard for me to tell which is index, which is value and so forth in your example, but how about this single select:
SELECT DISTINCT key, value FROM theTable; This lists all distinct key-value possibilities. Or, SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable) GROUP BY key, value HAVING count() > 1; This lists all key-value pairs with more than one value for the key. Roman ________________________________________ From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of David Raymond [david.raym...@tomtom.com] Sent: Thursday, October 11, 2018 12:23 PM To: SQLite mailing list Subject: Re: [sqlite] Find key,value duplicates but with differing values Maybe ... group by partId, name having count(distinct xmd.value) > 1; ? -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, October 11, 2018 12:00 PM To: General Discussion of SQLite Database Subject: [sqlite] Find key,value duplicates but with differing values I can find duplicates fine: select xmd.partId, parts.title, xmd.name, count(*) "#dupplicates", group_concat(xmd.value) "values", group_concat(xmd.idx) "indexes" from extra_meta_data xmd join parts on parts.id = xmd.partId group by partId, name having "#dupplicates" > 1; but most actual duplicates have the same value, so are harmless. so I'd like to select only the xmd.name rows which have differing values. Knowing that there can be more than 2 duplicates, i.e. a slef-join is not enough I think. Any hints on how to go about this problem please? Thanks, --DD _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
