Get Min code where there''s more than one

• Tom Currier

  Old Hand

  Points: 375

  More actions

  November 1, 2005 at 4:12 pm

  #102897

  code nbr

  010 1

  020 1

  030 1

  040 1

  050 1

  060 1

  070 1

  080 1

  090 1

  100 1

  110 39

  120 7

  130 8

  140 8

  Help - I need to write a query that returns code 110, e.g., the minimum code value where there are more than one record {e.g. nbr is the count of records} existing.  I seem to always struggle with multiple aggregates

  Thanks

  Thanks for suggestions, and sorry - I figured this out myself; here's the actual code

  SELECT MIN(NEIGHBORHOODS.NEIGHBORHOOD_CODE) AS START

  FROM (SELECT NEIGHBORHOOD_CODE, COUNT(NEIGHBORHOOD_CODE) AS TOTAL_RECORDS

   FROM SFCVB_NEIGHBORHOODS

   GROUP BY NEIGHBORHOOD_CODE) NEIGHBORHOODS

  WHERE TOTAL_RECORDS > 1

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  November 1, 2005 at 5:07 pm

  #601708

  select MIN(nbr), code

  from yourtable

  group by code

  having count(nbr) > 1

   

   

  is that what your talking about???

  
  
  A.J.
  DBA with an attitude

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  November 1, 2005 at 5:08 pm

  #601709

  ok, I read it again and saw that I misunderstood the first time.

  like this:

   

  select MIN(code)

  from yourtable

  having count(code) > 1

  
  
  A.J.
  DBA with an attitude

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 1, 2005 at 8:11 pm

  #601717

  SELECT MIN(NEIGHBORHOOD_CODE)

  FROM (SELECT NEIGHBORHOOD_CODE

                   COUNT(NEIGHBORHOOD_CODE) AS TOTAL_RECORDS, 

            FROM SFCVB_NEIGHBORHOODS

            GROUP BY NEIGHBORHOOD_CODE)

  WHERE TOTAL_RECORDS > 1

  _____________
  Code for TallyGenerator