March 10, 2010 at 2:18 pm
I have a simple select statement that has two geographic data types (SQL R2).
How can I make these fields distinct?
select
z.country,
z.city,
z.state,
z.Country,
z.GeogCol1,
z.GeogColTemp,
z.zipcode,
l.data_date,
l.loan,
l.mtgrlastname,
l.mtgrfirstname,
l.propertystreetnum,
l.propertydirection,
l.propertystreet,
l.cityname,
l.propertystate
from ZipCodes
Msg 421, Level 16, State 1, Line 1
The geography data type cannot be selected as DISTINCT because it is not comparable.
March 11, 2010 at 8:51 am
Hi,
As SQL tells you, you can't use DISTINCT on a geography column. What do you store exactly in your geo columns ? If they are simple points, you can use Geocolumn.Lat and Geocolumn.Long. It will give you two real numbers, which can be used with DISTINCT.
I hope this helps,
Laurent
February 9, 2016 at 12:14 am
You can use STAsBinary() (Returns the Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation of a geometry instance.)on geometry types to convert into binary format which can be used in distinct.
You can convert back to geometry by using STGeomFromWKB.
May 6, 2016 at 9:49 am
Create a CTE that has a ROW_NUMBER column, and then select rows where that column = 1 ROW_NUMBER() = 1
WITH src AS (
select
z.country,
z.city,
z.state,
z.Country,
z.GeogCol1,
z.GeogColTemp,
z.zipcode,
l.data_date,
l.loan,
l.mtgrlastname,
l.mtgrfirstname,
l.propertystreetnum,
l.propertydirection,
l.propertystreet,
l.cityname,
l.propertystate,
ROW_NUMBER() OVER (PARTITION BY l.propertynumber, l.propertystreet, z.zipcode ORDER BY l.propertynumber) AS idx
from ZipCodes)
SELECT * FROM src WHERE idx = 1;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply