You are correct. Value should/ could be inside count(), but not in group.
Sent from my T-Mobile 4G LTE Device -------- Original message -------- From: R Smith <ryansmit...@gmail.com> Date: 10/11/18 4:29 PM (GMT-05:00) To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Find key,value duplicates but with differing values On 2018/10/11 9:53 PM, Roman Fleysher wrote: > 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. If I may - this won't work directly as-is since the query: SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable) GROUP BY key, value HAVING count() > 1; will group by Key,Value and then output which Key-value pairs exhibit more than one instance (count) - which is physically impossible since counting the duplicate values from a distinct set is like asking the number of genders among American presidents before 2018.... The answer is always 1. If, in the outer query, you Select for (and group by) Key only, then it works. > > 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 _______________________________________________ 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
