Posted by andylole on 02/02/07 05:16

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

• Next in forum: Re: How to remove rows where only part of the row is duplicated
• Prev in forum: Re: SQL a range in the all dataset
• Thread view: How to remove rows where only part of the row is duplicated

[Reply to this message]