On Sun, 10 Mar 2013 20:36:47 +0530 "Navaneeth.K.N" <navaneet...@gmail.com> wrote:
> select distinct(lower(pattern)) as pattern, id from symbols where > value1 = ?1 or value2 = ?1 group by pattern Igor's answer is correct. I just want to point out what looks like a misunderstanding on your part regarding "distinct" because it may help you to understand Igor's answer better. You wrote select distinct(lower(pattern)) as though "distinct" were a function. It's not. The "distinct" keyword modifies the query such that distinct *rows* are returned. The whole row, that is, not a particular column. > "chu", "20851" > "chchu", "20879" > "cchu", "20907" > "chu", "20935" These are already distinct rows. The two you're thinking about "chu", "20851" and "chu", "20935" are distinct because they differ in their "id" column. That leaves you with a small problem: you want a distinct set of patterns, but for each pattern you need an id, and some patterns have more than one id. Whenever you want "one of Y" or "something about Y" for every X, you need a GROUP BY clause. Your X is "pattern" and your Y is "id". But which id? Igor suggests you take the minimum one, select lower(pattern) as pattern, min(id) as minid ... group by pattern which is certainly a fine choice if you don't care. HTH. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users