help with a sub-query 1

[loaded111]

Hi to everyone out there, I am writing a web-site which lists films. One of the tables is a customers. I have been asked to provide a list of all the customers who reside in the town with the most registrations. For example ten customers in Glasgow, five in Edinburgh etc. The output should cater for two or more towns satisfying the query. I know I need to use subquerys to return the values from this table in terms of count the max number of towns etc but am totally stuck as to what to do next. Would someone out there help me please?

The customer table looks like:
customerid
surname
forename
DOB
Address
Town
postcode
phonenumber
email

Thanks in advance for any help you can manage.

 

[PHV]

A starting point:
SELECT A.* FROM tblCustomers A
WHERE A.Town = (SELECT Town FROM (
SELECT TOP 1 Town, Count(*) As Registrations FROM tblCustomers GROUP BY Town ORDER BY 2 DESC) B)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[loaded111]

thats pretty good PHV, very useful. I ran it in access and it only gives me the top town. I guess I need the top two or more towns satisfying the query. so if you had ten customers in Glasgow and ten in Edinburgh they would all appear on the list not just Glasgow. Can you suggest a way of doing this? thanks in advance.

 

[PHV]

For the 5 top towns:
WHERE A.Town In (SELECT Town FROM (
SELECT TOP 5 Town, Count(*) As Registrations FROM tblCustomers GROUP BY Town ORDER BY 2 DESC) B)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244