SQL syntax: select only if the result is greater than X

Question

I have a table with dimensions called measures. The table has one column for the location and a second column for the corresponding value (the example is simplified).

The table looks like (note the 2 entries for loc1):

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

Now I want to formulate an SQL query (I actually use sqlite) that returns only the first two rows of the table (i.e., loc + value1 and loc1 + value2), because this location contains more than one record in this table.

the wording of the pseudo-text will be: show me rows of locations that are present more than once in the whole table
pseudo code:

 SELECT * from measures WHERE COUNT(location over the whole table) > 1 

The solution may be really simple, but somehow I do not seem to have cracked the nut.

that I am still a SELECT statement that returns locations with more than one record. as the next step, I will need exactly all the rows corresponding to the locations returned from this query:

 SELECT location FROM measures GROUP BY location HAVING count(*) > 1 

so in the next step I tried to make a JOIN with the same table and include the query above, but the results are incorrect. I tried 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!

Answer 1

You were right to use HAVING and think about using self-join ... the order of operations just changed a bit ...

 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-selection gets all locations with more than one record ... and then combines with the table again to get full results.

SQL Fiddle

Answer 2

Use the nested selection:

 SELECT location,value,type,value_added FROM measures WHERE location IN (SELECT location FROM measures GROUP BY location HAVING COUNT(*)>1) 

(Syntax from memory, may be somewhat off)

Answer 3

The idea is to get a list of locations with more than one value. To select records, use in :

 select m.* from measures m where m.location in (select location from measures group by location having count(*) > 1); 

You can also formulate this using a join:

 select m.*, mdup.numdups from measures m join (select location, count(*) as numdups from measures group by location having count(*) > 1 ) mdup on m.location = mdup.location; 

One of the benefits of running a query is that you can get the number of duplicates.

Answer 4

 SELECT * FROM measures WHERE (location) IN ( SELECT location FROM measures GROUP BY location HAVING COUNT(location) > 1 ) ORDER BY ASC