sqlite - SQL syntax: select only if more than X results -

i have table measurements called measures. table has 1 column location , second colum corresponding value (example simplified).

the table looks (note 2 entries loc1):

location | value ----------------- loc1     | value1 loc1     | value2 loc2     | value3 loc3     | value4 loc4     | value5 

i want formulate sql query (actually use sqlite) returns first 2 rows of table (i.e. loc+value1 , loc1+value2), because location has more 1 entry in table.

the pseudotext formulation be: show me rows of locations, present more once in whole table
pseudcode:

select * measures count(location on whole table) > 1 

the solution may simple, somehow seem not crack nut.

what have far select statement, returns locations have more 1 entry. next step need rows correspond locations returned query:

select location measures group location having count(*) > 1 

so next step tried join same table , incorporate above query, results incorrect. tried this, wrong:

select t1.location, t1.value       measures t1      join       measures t2 on t1.location = t2.location  group       t2.location  having count(*) > 1 

help appreciated!

you right use having, , think using self-join... had order of operations off...

select m1.location, m1.value measures m1 join (   select location   measures   group location   having count(*) > 1 ) m2 on m2.location = m1.location 

the sub-select gets locations have more 1 entry... , joined table again full results.

sql fiddle