select distinct but not quite - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

select distinct but not quite

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-19 : 01:26:16

Hi,

Not sure if this is possible to do in an efficient manner but I'd love to see if anyone has any input.

I have the following query I am running


SELECT  city,   longitude,   latitude, MAX(ViewDate) as viewDate FROM tblMapViews_Log  

GROUP BY	City,
		Latitude,
		Longitude

The problem is that altho it is returning exactly what I am asking it to, its bringing back more records than we would like to display.

The longitude and latitude are both accurate to 6 decimal places, so what is happening is we are bringing back some cities that are so close they are effectively the "same" on the map. (We are drawing x,y markers according to these positions)

I am not yet sure how many degrees difference I should have as seperation for two cities to be considered the same point of reference but I am wondering how would I go about doing this regardless? Perhaps I could have this value as a variable in my constructed query and play around with it.

Is what I am saying making sense? Basically to sum it up I want to do a SELECT DISTINCT on a rounded version of these numbers. How rounded I am not sure yet, but would like to be able to manipulate it.

If anyone can point me in the right direction, please do!

Much appreciated,
mike123

The table is as shown below

CREATE TABLE [dbo].[tblMapViews_Log](
[city] [varchar](50) NOT NULL,
[longitude] [numeric](18, 6) NOT NULL,
[latitude] [numeric](18, 6) NOT NULL,
[viewDate] [smalldatetime] NOT NULL
) ON [PRIMARY]

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 01:36:28

[code]SELECT City.
Longitude,
Latitude,
ViewDate
FROM (
SELECT City,
Longitude,
Latitude,
ViewDate,
ROW_NUMBER() OVER (PARTITION BY City, ROUND(Longitude, 4), ROUND(Latitude, 4) ORDER BY ViewDate DESC) AS RecID
FROM tblMapViews_Log
) AS x
WHERE RecID = 1[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 01:56:02

You also only need

NUMERIC (9, 6)

since the integer part is always less than 180 and greater than -180.
You save space with this. Instead of 9 bytes per number (18 bytes per record) you will only occupy 5 bytes per number (10 byte per record), for the numeric values.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-04-19 : 03:31:30

Hi Peso,

I ran your query, and compared it to the original. In this case they are both returning 6288 records, same order. I ran it with a variety of values passed to the round function (1,2,3,4) I think this is where it gets tricky to both explain and execute :)

Let's say for instance we have the following 4 records. ( I am making these numbers up)

NORTH VANCOUVER -123.133000 49.250000
WEST VANCOUVER -123.000000 49.000000
EAST VANCOUVER -122.975000 49.000000
SOUTH VANCOUVER -122.850000 49.000000

You can see that all these cities have their distinct cityName. It's situations like these where I want to be able filter which records should be brought back by there closeness in "longitude" and "latitude" values. For instance with these above 4 values since they are so close, my distance filter might bring this back as one spot. (preferably the one with the latest viewDate)

Is this query still correct and I'm not executing properly? Or does it need some slight modification?

Oh and excellent tip on saving me column space! It's a large table so I could definately use it :)

Thanks very much! much appreciated !
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-19 : 03:36:13

Delete the CITY part in the PARTITION clause.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources