SQL syntax: select only if more than X results
select, sql, sqlite
Solution
You were right to use `HAVING`, and to think about using the self-join... just had the order of the operations slightly off...
select m1.location, m1.value
from measures m1
join (
select location
from measures
group by location
having count(*) > 1
) m2 on m2.location = m1.location
The sub-select gets all the locations that have more than one entry... and then this is joined to the table again to get the full results.
SQL Fiddle
Problem
I have a table with measurements called measures. The table has one column for the location and a second colum for a corresponding value (example is simplified). The table looks like (note 2 entries for loc1): ``` location | value ----------------- loc1 | value1 loc1 | value2 loc2 | value3 loc3 | value4 loc4 | value5 ``` i now want to formulate a SQL query (actually i use sqlite) which only returns the first two rows of the table (i.e. loc+value1 and loc1+value2), because this location has more than one entry in this table. the pseudotext formulation would be: show me the rows of the locations, which are present more than once in the whole table pseudcode: ``` SELECT * from measures WHERE COUNT(location over the whole table) > 1 ``` the solution may be really simple, but somehow i seem not to crack the nut. what i have so far is a SELECT statement, which returns locations which have more than one entry. as a next step i would need exactly all rows which correspond to the locations returned from this query: ``` SELECT location FROM measures GROUP BY location HAVING count(*) > 1 ``` so as a next step i tried to do a JOIN with the same table and incorporate above query, but the results are incorrect. i tried it like this, but this is wrong: ``` select t1.location, t1.value from measures as t1 join measures as t2 on t1.location = t2.location group by t2.location having count(*) > 1 ``` help is appreciated!