On Fri, May 13, 2016 at 10:14 AM, Dominique Devienne <ddevienne at gmail.com> wrote:
> Imagine I have a single table containing a single text column, of user > names. > I'm trying to find user names which differ only by case. > > > select count(*) from os_users > 1969 > > > select u1.user, u2.user from os_users u1, os_users u2 where > upper(u1.user) = upper(u2.user) and u1.user <> u2.user > foo|FOO > FOO|foo > > > select user from os_users u where exists (select 1 from os_users u2 > where upper(u2.user) = upper(u.user) and u2.user <> u.user) > foo|FOO > FOO|foo > > I tried both queries above, but they are slow (almost 3s, with close > to 4M steps, i.e. O(N^2)), and ideally I'd like a query returning only > one row. And that's w/ or w/o a PK or UNIQUE INDEX on the one column. > > > select group_concat(user) from os_users group by upper(user) having > count(*) > 1 > foo,FOO > > I did manage the above, which returns in 7ms, and get me what I want, > but in aggregated form (thus needs reparsing). Is there a query that > can return just foo|FOO, i.e. the pairs of mixed-case matches > efficiently? > ?how about: SELECT group_concat(user,"|") FROM os_users GROUP BY upper(user) HAVING count(*) > 1 ?? > > Thanks, --DD > -- The unfacts, did we have them, are too imprecisely few to warrant our certitude. Maranatha! <>< John McKown
