SQL in R: HAVING condition with only the condition of one row?-CodePudding

I am learning to use SQL in R. I want to select cities that are more northern than Berlin from a dataset.

For this, I have tried:

sql4 = "SELECT Name, Latitude FROM city HAVING Latitude > Latitude(Berlin)"

result4 = dbSendQuery(con, sql4)

df4 = dbFetch(result4)

head(df4)

and

sql4 = "SELECT Name, Latitude FROM city HAVING Latitude > (Name IN 'Berlin')"

result4 = dbSendQuery(con, sql4)

df4 = dbFetch(result4)

head(df4)

Neither syntax works unfortunatley. So my question is: How do I select all cities "north from Berlin", i.e. latitude value higher than that of the Name row 'Berlin'? Is there a different, better approach?

CodePudding user response：

Assuming Berlin occur at most once in the city table, you may use:

SELECT Name, Latitude
FROM city
WHERE Latitude > (SELECT Latitude FROM city WHERE Name = 'Berlin');

You want to be using WHERE here to filter, rather than HAVING. HAVING is for filtering aggregates when using GROUP BY, which you are not using.

CodePudding user response：

You cannot actually use Latitude(Berlin), I think. I typically use something like this:

SELECT Name, Latitude FROM city WHERE Latitude = (SELECT Latitude from city WHERE Name = "Berlin")

Hope this helps. -Suhas