Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Help finding more than 1 occurrence in a table

    Hello! I need help finding cities with more than 1 postal code.

    Table: main
    Columns:
    - cities
    - streets
    - postalcodes

    Thanks a lot!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try the following:

    SELECT cities
    FROM (SELECT DISTINCT cities, postalcode FROM main) AS citiespostal
    GROUP BY cities
    HAVING COUNT(*) > 1;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    Code:
    SELECT cities
     FROM  main
     GROUP BY
           cities
     HAVING
           COUNT(DISTINCT postalcode) > 1
    ;
    Last edited by tonkuma; 02-07-13 at 14:59. Reason: Add a blank before HAVING

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Very elegant. I like it!!
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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