A correlated subquery:
select *
from t
where (select count(*)
from t as b
where b.data1 = t.data1) >= 3;
or with a subselected set of valid rows:
select *
from t
where data1 in (select data1
from t as b
group by data1
having count(*) >= 3);
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-----Original Message-----
>From: [email protected] [mailto:sqlite-users-
>[email protected]] On Behalf Of Hajo Locke
>Sent: Tuesday, 13 January, 2015 00:30
>To: [email protected]
>Subject: [sqlite] help with query
>
>Hello list,
>
>i have a problem finding right query, hope you can help me.
>I have a sample table like this: http://pastebin.com/8qyBzdhH
>I want to select all lines where at least 3 lines in column data1 have
>same value.
>My expected result-set ist this: http://pastebin.com/UcaXLVx9
>How can this be done in one query?
>I tested with something like this:
>SELECT *,count(*) as mycount FROM `table` group by data1 having
>mycount>=3;
>But this results in summarized output, but i need every single line.
>I would need something like: select * from `table` where count(data1)>3;
>But this is not allowed.
>Do you have any hints for me?
>
>Thanks,
>Hajo
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
