Step 1: count the occurrences: SELECT data1,count() AS count FROM table GROUP BY data1;
Step 2: get the rows with a count above the limit SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count >= 3; Step 3: get the keys from the rows SELECT data1 FROM (SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count >= 3); Step 4: retrieve the original rows SELECT * FROM table WHERE data1 IN (SELECT data1 FROM (SELECT data1,count() AS count FROM table GROUP BY data1 HAVING count >= 3)); -----Ursprüngliche Nachricht----- Von: Hajo Locke [mailto:hajo.lo...@gmx.de] Gesendet: Dienstag, 13. Jänner 2015 08:30 An: sqlite-users@sqlite.org Betreff: [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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
