MULTIPLE COLUMNS using and GROUP BY

I hope there's someone that is able to help me. I'm new to SQL Server 2005 and have come across a pretty tough problem.

I need to return City Names from the City Table ordered by province and postal code. However, SELECT statement returns multiple occurrences of a City_Name from the Cities table. I only want the City Name to appear once though.

Here's the SQL statement:

"SELECT City_Name, Province_ID, City_PostCode
FROM Cities
WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode
AND City_Name != '" + exclude
ORDER BY Province_ID ASC, City_PostCode ASC ";

I've tried to use distinct on the City Name but that didn't work. It seems the distinct keyword has to use all the column names to return a distinct record. An alternative would be to GROUP the records by City_Name however you have to use all the columns that were selected leaving me with the same distinct keyword problem.

Can anyone help me?

hi,

Well, if you have different zip codes to the same City, you should have duplications and no matter what you use; distinct or group by.

eg:

City_name Province_ID City_PostCode

Los Angeles CA 90150

Los Angeles CA 90151

In this case, you should not have one record, based on your select statement. Maybe you should appear a zip code range, instead of individual zips, then you should have distinct result set.

Also, if just change the second zip to the first one, and using distinct you will have one record. you do not need to specify column level the distinct, it applied on all enumerated column in the select statement.

I hope it helps.

Kind Regards,

Janos

|||

If you want to show the multiple postal codes as single row then you can use the following query.. One row per city with out data loose..

Code Snippet

Create Table #cities (

[City_name] Varchar(100) ,

[Province_ID] Varchar(100) ,

[City_PostCode] Varchar(100)

);

Insert Into #cities Values('Los Angeles','CA','90150');

Insert Into #cities Values('Los Angeles','CA','90151');

Insert Into #cities Values('Austin','TX','73301');

Select

Distinct

Main.City_name

, Main.Province_ID

, Substring((Select ',' + City_PostCode as [text()]

From #cities Sub

Where Sub.City_name = Main.City_name

And Sub.Province_ID = Main.Province_ID For XML Path('')),2,8000) as [City_PostCodes]

From

#cities as Main

|||Thanks Jano, I updated the statement to reflect the following:

"SELECT DISTINCT City_Name, Province_ID FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' )
ORDER BY Province_ID ASC ";

The only thing is I can't sort the selections according to there postal codes. however the intial problem of the distinct fields is resolved.

Thanks again.

Regards,
Simon
|||

You can sort by postal_code (using OVER clause)..

"SELECT DISTINCT City_Name, Province_ID,Max([City_PostCode]) Over (Partition By City_name,Province_ID) as [City_PostCode] FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' ) ORDER BY [City_PostCode] ASC ";