distinct rows with more than one column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

distinct rows with more than one column

Author

Topic

drdream
Starting Member

29 Posts

Posted - 2007-04-08 : 06:21:13

How can I filter duplicate rows with more than one column in SQL Server?

I have a table with every city in the USA like


CityID   Cityname    Zipcode
------------------------------
1        New York    10001
2        New York    10002
3        Boston      02115
4        Boston      02116
5        Boston      02113
6        Manchester  30012

I tried Select Distinct(CityName) but it returns all rows because of the CityID and Zipcode. I need the CityID

I would like to get (I dont need the zipcode at this point)
--------------------------


1 New York
3 Boston
6 Manchester

More Info:
This is inside a UDF Function which returns the closest cities (table) from a given lon/lat. The Cities Table has multiple listings for each city because of the zip codes. And also each city/zip pair has an ID.

I was given this to try on yahoo answers:


select * from cities
where CityID in (
select min(CityID) from CityID
group by Cityname
)

That actually does do the trick BUT for some reason its not returning the same cities as without it (seems to be skipping cities)

and finally my UDF


CREATE FUNCTION dbo.fnFindNearestCities

(
    @Lat1 DECIMAL(10,6),
    @Lon1 DECIMAL(10,6)
)
RETURNS TABLE

AS

RETURN SELECT Miles = dbo.CalculateDistance(@Lat1, @Lon1, Lat, Lon), 
              CityName, 
              ZipPostal 
            FROM 
              US_Cities 

            WHERE Miles IS NOT NULL 
        

GO

Cities Table


CREATE TABLE [US_Cities] (
	[CityID] [int] IDENTITY (1, 1) NOT NULL ,
	[CityName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CountryID] [int] NULL ,
	[Rank] [int] NULL ,
	[Lon] [float] NULL ,
	[Lat] [float] NULL ,
	[StateID] [int] NULL ,
	[MajorCity] [bit] NULL ,
	[NAC] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Population] [bigint] NULL ,
	[ZipPostal] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StateAbbr] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-08 : 09:30:37

[code]
select min(CityID) as CityID, Cityname
from US_Cities
group by Cityname
[/code]

drdream
Starting Member

29 Posts

Posted - 2007-04-08 : 13:43:09

Tried that but since Lat and Lon are used in the function, they have to be included


select top 50 min(CityID) as CityID, Cityname, Miles = dbo.CalculateDistance(@Lat1, @Lon1, Lat, Lon)    
from   US_Cities
group by Cityname

Server: Msg 8120, Level 16, State 1, Line 31
Column 'US_Cities.Lon' is invalid in the select list because it is not contained in either an aggregate function

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-08 : 18:03:07

[code]
select CityID, CityNames, iles = dbo.CalculateDistance(@Lat1, @Lon1, Lat, Lon)
from US_Cities c
inner join
(
select min(CityID) as CityID, Cityname
from US_Cities
group by Cityname
) m
on c.CityID = m.CityID
[/code]

drdream
Starting Member

29 Posts

Posted - 2007-04-09 : 02:26:22

This is the problem im having.

Original query returns below


DECLARE    @Lat1 DECIMAL(10,6)
DECLARE    @Lon1 DECIMAL(10,6)
--    @Results int
SET @Lat1 = 46.326160000000002
SET @Lon1 = -96.528030000000001

SELECT Distinct Top 20 CityName, CityID, Miles = dbo.CalculateDistance(@Lat1, @Lon1, Lat, Lon) 
              --CityName
              --ZipPostal 
            FROM 
              US_Cities 
ORDER BY Miles
-------------------------------

-- Returns


CityName        CityID  Miles   
---------------------------------
Boston	        45001	.000
Boston	        45014	.560
Boston	        45028	.560
Revere	        51494	.560
Chelsea	        51493	.793
Boston	        45013	1.483
Boston	        45009	1.585
Boston	        45010	1.772
Boston	        51548	1.772
Boston	        51541	1.859
Boston	        51540	1.942
Boston	        51543	1.942
Boston	        51554	1.942
Charlestown	45029	2.097
Boston	        45011	2.171
Winthrop	51495	2.378
Boston	        45006	2.629
Everett	        51492	2.858
Boston	        45027	2.912
Cambridge	51484	2.912

-------------------------------

New Query from khtan


select top 20 c.CityID, c.CityName, miles = dbo.CalculateDistance(@Lat1, @Lon1, Lat, Lon)    
from   US_Cities c
inner join 
(
    select min(CityID) as CityID, Cityname    
    from   US_Cities
    group by Cityname
) m
on c.CityID = m.CityID
Order By Miles

Returns


CityID  CityName        Miles 
-----------------------------
45001	Boston	         .000
45029	Charlestown	2.097
51478	Brighton	4.231
51477	Allston	        4.484
51489	Brookline	5.347
51491	Malden	        5.463
51480	Readville	5.905
45026	Mattapan	6.241
45030	Jamaica Plain	6.390
44930	Nahant	        6.632
44928	Saugus	        7.177
44923	Lynn	        7.499
45031	Roslindale	7.664
51510	Chestnut Hill	8.083
51630	Newton Center	8.388
44921	Winchester	8.518
51632	Newton Highlands9.141
45032	West Roxbury	9.192
44915	Wakefield	9.192
51633	Newton Lower Falls9.462

While khtan's query works at eliminating the duplicates, for some reason its skipping Revere, Chelsea, etc from the first function and they are closer in real life. Could it be the distance function?




distance function code
[code]
ALTER  FUNCTION dbo.CalculateDistance 
( 
    @Lat1 DECIMAL(10,6),
    @Lon1 DECIMAL(10,6),
    @Lat2 DECIMAL(10,6),
    @Lon2 DECIMAL(10,6)
) 
RETURNS DECIMAL(12,3) 
AS 
BEGIN 
    DECLARE 
        @rads DECIMAL(10,8), 
        @dist DECIMAL(12,3), 
        @calc DECIMAL(10,8) 
 
      SET @rads = 57.29577951
 
     SELECT 
        @lat1 = @lat1 / @rads, 
        @lon1 = @lon1 / @rads, 
        @lat2 = @lat2 / @rads, 
        @lon2 = @lon2 / @rads 
 
    IF @lat1 = @lat2 AND @lon1 = @lon2 
        SET @dist = 0.00 
    ELSE 
    BEGIN 
        SET @calc = SIN(@lat1) * SIN(@lat2) + COS(@lat1) 
            * COS(@lat2) * COS(@lon1 - @lon2) 
        IF (@calc) > 1.0 
            SET @calc = 1.0 
        SET @dist = 3963.1 * ACOS(@calc) 
    END 
 
    RETURN (@dist) 
END

sorry for the long post!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-09 : 02:33:24

[code]
declare @US_Cities table
(
CityName varchar(20),
CityID int,
Miles decimal(6,3)
)
insert into @US_Cities
select 'Boston', 45001, .000 union all
select 'Boston', 45014, .560 union all
select 'Boston', 45028, .560 union all
select 'Revere', 51494, .560 union all
select 'Chelsea', 51493, .793 union all
select 'Boston', 45013, 1.483 union all
select 'Boston', 45009, 1.585 union all
select 'Boston', 45010, 1.772 union all
select 'Boston', 51548, 1.772 union all
select 'Boston', 51541, 1.859 union all
select 'Boston', 51540, 1.942 union all
select 'Boston', 51543, 1.942 union all
select 'Boston', 51554, 1.942 union all
select 'Charlestown', 45029, 2.097 union all
select 'Boston', 45011, 2.171 union all
select 'Winthrop', 51495, 2.378 union all
select 'Boston', 45006, 2.629 union all
select 'Everett', 51492, 2.858 union all
select 'Boston', 45027, 2.912 union all
select 'Cambridge', 51484, 2.912

select top 20 c.CityID, c.CityName, Miles
from @US_Cities c
inner join
(
select min(CityID) as CityID, CityName
from @US_Cities
group by CityName
) m
on c.CityID = m.CityID
Order By Miles

/*
CityID CityName Miles
----------- -------------------- --------
45001 Boston .000
51494 Revere .560
51493 Chelsea .793
45029 Charlestown 2.097
51495 Winthrop 2.378
51492 Everett 2.858
51484 Cambridge 2.912
*/
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources