[RESOLVED] Return only lowest zip code

[mrbaseball34]

Got this SQL:

    SELECT DISTINCT(LocCity), LocZipCode FROM exp_course_events order by LocCity

and this data:

    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Aguadilla', '00602');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Akron', '44300');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Akron', '44333');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Albany', '12205');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Albuquerque', '87102');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Albuquerque', '87109');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Austin', '78741');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Austin', '78753');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Austin', '78757');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Bend', '97701');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('San Antonio', '78200');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('San Antonio', '78201');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('San Antonio', '78207');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('San Diego', '92108');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('San Francisco', '94111');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('San Juan', '00926');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Santa Clara  (San Jose)', '95054');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Springdale', '72762');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Springfield', '62703');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Springfield', '62711');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Springfield', '65803');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('St. Louis', '63105');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Visalia', '93291');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Waco', '76705');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Warwick', '02886');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Waukesha', '53186');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('Waukesha', '53188');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('West Chester', '45069');
    INSERT INTO `exp_course_events` (`LocCity`, `LocZipCode`) VALUES ('West Des Moines', '50300');

Need to return only one value for LocZipCode for each LocCity, prefereable the lowest number LocZipCode
for that LocCity.

This is results I'd like:

    Aguadilla, 00602
    Akron, 44300
    Albany, 12205
    Albuquerque, 87102
    Austin, 78741
    Bend, 97701
    San Antonio, 78200
    San Diego, 92108
    San Francisco, 94111
    San Juan, 00926
    Santa Clara (San Jose), 95054
    Springdale, 72762
    Springfield, 62703
    St. Louis. 63105
    Visalia, 993291
    Waco, 76705
    Warwick, 02886
    Waukesha, 53186
    West Chester, 45069
    West Des Moines, 50300

[bradgrafelman]

Simply GROUP BY the column(s) you want to group on, and then ORDER the results in ASCending order by zip code (giving you only the lowest one in each group).

[mrbaseball34]

Not sure what you mean, why would that return only ONE of each city?

[bradgrafelman]

mrbaseball34;10986553 wrote:

Not sure what you mean, why would that return only ONE of each city?

If the city is what you're GROUP'ing on, then yes, you'll only get one row per group.

[mrbaseball34]

SELECT LocCity, LocZipCode FROM exp_course_events group by LocCity order by LocZipCode ASC 

doesn't show the lowest zip for each city, just some of them.

[mrbaseball34]

That's good enough, Brad, thanks.

[bradgrafelman]

Woops, it's been ages since I've done a lot of SQL-centric work.

To actually get the lowest zip for each city, you'd have to do a self-join or use a subquery.