Results 1 to 2 of 2

Thread: Max

  1. #1
    Join Date
    Jan 2010
    Posts
    1

    Unanswered: Max

    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.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •