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
