Max

MisterSin · #1 (**permalink**) 01-03-10, 03:21

Hello, I'm making some queries for a school project concerning spatial data. Now the spatial data part goes just fine, however I have a general SQL problem.

We use IBM's DB2. This is my query:

Code:

SELECT Province, Neighbours
FROM(
SELECT DISTINCT p1.provname AS Province, COUNT(p1.provname) AS Neighbours
FROM province p1, province p2
WHERE db2gse.ST_Touches(p1.provinceColumn, p2.provinceColumn) = 1 AND NOT p1.provname = p2.provname
GROUP BY p1.provname
ORDER BY Neighbours DESC
) AS MaxProvince(Province, Neighbours)
WHERE Neighbours = (SELECT MAX(Neighbours) FROM MaxProvince.Neighbours)

The assignment was to select the province(s) with the most neighbours. Therefore I compute a new table with for each province an entry with it's amount of neighbours. This goes just fine.

However, I don't find how I can select the province(s) with the most amount of neighbours from that new table. Whenever I try to execute my code, it says 'SQL0204N "S0726288.MAXPROVINCE" is an unidentified name. SQLSTATE=42704'.

Can anyone help me with this? It'd be greatly appreciated.

sco08y · #2 (**permalink**) 01-06-10, 14:38

I think it should just be Neighbours instead of MaxProvince.Neighbours. You select from a table, not a column. I'm also not familir with the AS table(column, column...) syntax, but it looks like its redundant since you named those columns with aliases already.

You might also try rewriting it using a WITH clause; I just find it a bit more readable:

Code:

WITH MaxProvince AS
(
SELECT DISTINCT p1.provname AS Province, COUNT(p1.provname) AS Neighbours
FROM province p1, province p2
WHERE db2gse.ST_Touches(p1.provinceColumn, p2.provinceColumn) = 1 AND NOT p1.provname = p2.provname
GROUP BY p1.provname
ORDER BY Neighbours DESC
)

SELECT Province, Neighbours
FROM AS MaxProvince
WHERE Neighbours = (SELECT MAX(Neighbours) FROM MaxProvince)

It's slightly more verbose, but it breaks it into logical units for your reader.

Internet Services

Web Development

Digital Marketing

Consumer Tech