On Sun, 10 Mar 2013 20:36:47 +0530
"Navaneeth.K.N" <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
