Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2014
    Posts
    1

    Unanswered: how to select the same record in sqlite?

    C:\Documents and Settings\Administrator>d:\sqlite3 d:\test.db
    SQLite version 3.8.5 2014-06-04 14:06:34
    Enter ".help" for usage hints.
    sqlite> create table test(f1 TEXT,f2 TEXT, f3 TEXT);
    sqlite> insert into test values("x1","y1","w1");
    sqlite> insert into test values("x1","y2","w1");
    sqlite> insert into test values("x1","y2","w2");
    sqlite> insert into test values("x1","y2","w3");
    sqlite> insert into test values("x1","y1","w1");
    sqlite> insert into test values("x1","y3","w1");
    sqlite> insert into test values("x1","y4","w1");
    sqlite> insert into test values("x3","y3","w4");

    1)please select the record which contain the same f1 and f2 values, and its rowid.

    Here is my method.
    sqlite> select rowid,f1,f2,count(f2) from test group by f1,f2;
    5|x1|y1|2
    4|x1|y2|3
    6|x1|y3|1
    7|x1|y4|1
    8|x3|y3|1

    i want the result as
    1|x1|y1
    2|x1|y2
    3|x1|y2
    4|x1|y2
    5|x1|y1

    2)please select the record which contain the same f1 and f2 and f3 values, and its rowid.
    Here is my method.
    sqlite> select rowid,f1,f2,f3,count(f2),count(f3) from test group by f1,f2,f3;
    5|x1|y1|w1|2|2
    2|x1|y2|w1|1|1
    3|x1|y2|w2|1|1
    4|x1|y2|w3|1|1
    6|x1|y3|w1|1|1
    7|x1|y4|w1|1|1
    8|x3|y3|w4|1|1
    what i want to get is :

    1|x1|y1|w1
    5|x1|y1|w1

    how can i rewrite my sqlite query command to get the right result?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just use a self join on the table test

    so there are 2 data matches x1 & y1, x1 & y2
    so depends on your logic but I make it 8 rows that match

    Code:
    select t1.rowid, t1.f1, t1.f2, t2.rowid as match from test as t1, test as t2
    where t1.f1 = t2.f1 and t1.f2 = t2.f2 and t1.rowid <> t2.rowid
    order by t1.f1,t2.f2
    Code:
    Row	F1	F2	Matches Row
    1	x1	y1	5
    5	x1	y1	1
    2	x1	y2	3
    2	x1	y2	4
    3	x1	y2	2
    3	x1	y2	4
    4	x1	y2	2
    4	x1	y2	3
    the second question is in essence the same as the first question, except you have to match on another field.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    10
    these what i love forum you can learn from other have knowledge about the problem they post, i also have that in my mind, again thanks for sharing that..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •