Wednesday, 7 August 2013

SQL syntax: select only if more than X results

SQL syntax: select only if more than X results

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, t1.type, t1.value_added
from
measures as t1
join
measures as t1 on t1.location = t2.location
group by
t2.location
having count(*) > 1
help is appreciated!

No comments:

Post a Comment