Hi,
Without using sqlite, I can sort the table test ('sort' is from
coreutils, sort on the 1st column then the 2nd column), then I use awk
to parse the sorted file to get what I need. Should this approach be
faster than the sqlite query (in terms of runtime, not in term of
programming time)?
On Thu, Aug 26, 2010 at 3:59 PM, David Bicking <dbic...@yahoo.com> wrote:
> you can use:
>
> select col1, col2 from test where col1 in (select col1 from test group by
> col1 having count(*)<=2);
>
> David
>
> --- On Thu, 8/26/10, Peng Yu <pengyu...@gmail.com> wrote:
>
>> From: Peng Yu <pengyu...@gmail.com>
>> Subject: [sqlite] How to get the original rows after 'group by'?
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Date: Thursday, August 26, 2010, 4:53 PM
>> Hi,
>>
>> In the following sql query, I want to get all the lines
>> which
>> satisfies that the first column appears less or equal to 2
>> times. Is
>> there a way to do it other than using inner join of the
>> original table
>> and the query in main.sql?
>>
>> $ cat main.sql
>> #!/usr/bin/env bash
>>
>> #sqlite3 foods.db <<EOF
>>
>> rm -rf main.db
>> sqlite3 main.db <<EOF
>>
>> create table test (col1 text, col2 text);
>> insert into test values('a1', 'b1');
>> insert into test values('a1', 'b2');
>> insert into test values('a1', 'b3');
>> insert into test values('a1', 'b4');
>> insert into test values('a2', 'b5');
>> insert into test values('a2', 'b6');
>> insert into test values('a3', 'b8');
>>
>> .mode column
>> .headers on
>> .echo on
>> select col1, col2 from test group by col1 having count(*)
>> <= 2;
>>
>> EOF
>>
>> $ ./main.sql
>> select col1, col2 from test group by col1 having count(*)
>> <= 2;
>> col1 col2
>> ---------- ----------
>> a2 b6
>> a3 b8
>>
>> --
>> Regards,
>> Peng
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
