SEO

#1 (**permalink**) 03-01-2008, 12:13 PM

Hi,

I've got a db table containing 5 columns(excluding id) consisting of
1.) First Half of a UK postcode
2.) Town name to which postcode belongs
3.) Latitude of Postcode
4.) Longitude of Postcode
5.) Second Part of the Postcode

I want to select columns 1,2,3 and 4, but once only. There are often
several entries where 1 and 2 are the same but 3 and 4 are different
i.e.
WA1 Bewsey and Whitecross 53.386492 -2.596847
WA1 Bewsey and Whitecross 53.388203 -2.590961
WA1 Bewsey and Whitecross 53.388875 -2.598504
WA1 Fairfield and Howley 53.388455 -2.581701
WA1 Fairfield and Howley 53.396117 -2.571789

My current query is
SELECT DISTINCT Postcode, Town, latitude, longitude
FROM Postcode
WHERE Postcode.Postcode = 'wa1'
ORDER BY Postcode, Town

However as latitude and longitude differ on each line DISTINCT does
not do what I'm looking for.
Can anybody suggest a way changing the query to just give the first
instance of each Postcode/Town combo?
I.E.
WA1 Bewsey and Whitecross 53.386492 -2.596847
WA1 Fairfield and Howley 53.388455 -2.581701

Many thanks!
Drew

#2 (**permalink**) 03-01-2008, 12:13 PM

There isn't really any 'first' instance unless you
define your own ordering. However, assuming you have a unique ID
column
this should work

SELECT a.Postcode, a.Town, a.latitude, a.longitude
FROM Postcode a
WHERE NOT EXISTS (SELECT * FROM Postcode b
WHERE b.Postcode=a.Postcode
AND b.Town=a.Town
AND b.ID>a.ID)
ORDER BY a.Postcode, a.Town

#3 (**permalink**) 03-01-2008, 12:13 PM

(andylole@gmail.com) writes:
> I've got a db table containing 5 columns(excluding id) consisting of
> 1.) First Half of a UK postcode
> 2.) Town name to which postcode belongs
> 3.) Latitude of Postcode
> 4.) Longitude of Postcode
> 5.) Second Part of the Postcode
>
> I want to select columns 1,2,3 and 4, but once only. There are often
> several entries where 1 and 2 are the same but 3 and 4 are different
> i.e.
> WA1 Bewsey and Whitecross 53.386492 -2.596847
> WA1 Bewsey and Whitecross 53.388203 -2.590961
> WA1 Bewsey and Whitecross 53.388875 -2.598504
> WA1 Fairfield and Howley 53.388455 -2.581701
> WA1 Fairfield and Howley 53.396117 -2.571789
>
> My current query is
> SELECT DISTINCT Postcode, Town, latitude, longitude
> FROM Postcode
> WHERE Postcode.Postcode = 'wa1'
> ORDER BY Postcode, Town
>
> However as latitude and longitude differ on each line DISTINCT does
> not do what I'm looking for.
> Can anybody suggest a way changing the query to just give the first
> instance of each Postcode/Town combo?

A simple way out would be:

SELECT Postcode, Town, AVG(latitude), AVG(longitude)
FROM Postcode
GROUP BY Postcode, Town

Of course, this yield data that is not in the table at all, but it's a
reasonable assumption that the different lat/long values are in the same
proximity. And if they are not, you have a much bigger problem anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#4 (**permalink**) 03-01-2008, 12:13 PM

Erland,

Your solution is perfect!
Many thanks to all for helping.
Cheers,
Andy.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How to remove rows where only part of the row is duplicated