1

I'm trying to find the the lowest street address number for every street in my area. My results return the wrong value, so I need help understanding why it's returning the wrong value and how to correct it so I get the desired results.

I'm using SQLServer 2012

TABLE (A) columns:

ZONE_NUMBER (int, null),
Street_NUMBER (char(10), null),
Street_Name (varchar(255), not null),
Street_Type (char(5), null)

TABLE (A) values:

ZONE_NUMBER | STREET_NUM | STREET_NAME | STREET TYPE |
13             1502         COTTONWOOD    DR
13             1544         COTTONWOOD    DR
13             1564         COTTONWOOD    DR
13             720          COTTONWOOD    DR

Query:

SELECT 
   zone_number,
   min(street_number) as street_number
FROM
   A
GROUP by ZONE_NUMBER

Expected Results:

ZONE_NUMBER | STREET_NUMBER
13            720

Actual Results:

ZONE_NUMBER | STREET_NUMBER
13            1502

If I change from min() to max(), I get my desired results:

ZONE_NUMBER | STREET_NUMBER
13            720

Why? Is there something I'm missing?

Also, if '0' Street_number had it's own record, how can I skip '0' and get the next lowest result?

Thanks for any considerations for answering this problem.

closed as off-topic by RDFozz, LowlyDBA, hot2use, Michael Green, mustaccio Apr 5 '18 at 12:37

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – RDFozz, LowlyDBA, hot2use, Michael Green, mustaccio
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 5
    well, STREET_NUMBER is a char data type, so the order is alphabetically, and '1502' is smaller than '720' because '1' comes before than '7'. If there's always gonna be numbers, either use a numeric data type, or convert the result to one before using MIN or MAX – Lamak Apr 4 '18 at 20:28
  • That's what the issue was! Thanks for spotting that and answering so quickly. – aj1970 Apr 4 '18 at 20:31
  • 1
    This is a really common and nasty problem. I think you need to seriously consider why you want to sort these values, because in the real world the property addresses are rarely ordered conveniently. Address will also contains letters and characters for apartments and units. (10A and 10B 2/10 etc). A few houses near me are good examples. There is a 98B and 2/98B. The property behind me is 100E which comes before 100 which is the next house along the road, and there is no 100 B,C or D. – Sir Swears-a-lot Apr 5 '18 at 2:51
  • You may wish to rename your question "how to sort addresses" – Sir Swears-a-lot Apr 5 '18 at 2:52
1

Since you are using 2012, you can use TRY_CONVERT as Lamak alluded to if you can't change the street number data type. Note, if you have something other an an INT in this column, then your results will not contain these rows

SELECT 
   zone_number,
   min(try_convert(int,street_number)) as street_number
FROM
   A
GROUP by ZONE_NUMBER

Not the answer you're looking for? Browse other questions tagged or ask your own question.