list one record and only one record for each postcode in a database 1

[cloverdog]

I have a simple query which I am using to retrieve the first record found for each post code from a customer database. It doesn’t matter which of say 5 records for a postcode is returned but it must return one and only one.

I have tried using the first() function but I am either using it incorrectly or it is the wrong way to do what I need.

SQL used is as follows:

SELECT tblCustomer.[Post Code], [First Name]+" "+[Surname] AS FullName, tblCustomer.Street, tblCustomer.Town, tblContract.CustomerID, tblCustomer.County, tblCustomer.Latitude, tblCustomer.Longitude, "England" AS Country
FROM tblCustomer INNER JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID
WHERE (((tblContract.CustomerID)>1) AND ((tblCustomer.Latitude)>52.00514 And (tblCustomer.Latitude)<52.51912) AND ((tblCustomer.Longitude)>-0.37281 And (tblCustomer.Longitude)<1.01))
ORDER BY [First Name]+" "+[Surname];

Any help would be appreciated. Many thanks.

 

[GHolden]

Hi,

Use SELECT TOP 1 tblCustomer.[postcode].....



There are two ways to write error-free programs; only the third one works.

 

[cloverdog]

Thank you but I tried this. It only returns 1 record but I need one record for every postcode.

 

[PureSlate]

Try using something akin to this:

Select tblCustomer.[Post Code], First(Field1), First(Field2)
FROM tbl
GROUP BY tblCustomer.[Post Code]

Don't forget to GROUP BY the value you only want one of when using the FIRST() function!

 

[GHolden]

Ah... okay.

If you want one record for each postcode:

Assuming you have a unique ID on the tblCustomer

    SELECT 
        MAX(CustomerID) CustomerID
    FROM tblCustomer
    GROUP BY PostCode

would give you a list of one unique CustomerID for each Postcode.

You could then use this to link back to the original query similar to...

SELECT
	A.CustomerID,
        C.Surname + " " + C.FirstName,
        etc......
FROM
	(
	SELECT 
             MAX(CustomerID) CustomerID
	FROM tblCustomer
	GROUP BY PostCode
	) A
	JOIN tblCustomer C
	ON A.CustomerID = C.CustomerID

There are two ways to write error-free programs; only the third one works.

 

[cloverdog]

Hello

Thank you all for your help. I finally got the query working and in case anyone else wants it the simplified version is:

SELECT tblCustomer.[Post Code], tblCustomer.CustomerID
FROM tblCustomer
WHERE (((tblCustomer.CustomerID)=(SELECT Max(Temp.[CustomerID])
FROM tblCustomer as Temp
WHERE Temp.[Post Code] = tblCustomer.[Post Code])))
ORDER BY tblCustomer.[Post Code];

It now gives me the latest customer record for each postcode.