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
