help needed with Microsoft SQL Server syntax

Jimmy_inc
Jimmy_inc used Ask the Experts™
on
select 5 records from customers table for each city, so i guess something like select top 5 customerid from tablename group by city?  i would hope to return:

NY, CUSTOMERID1
NY, CUSTOMERID2
NY, CUSTOMERID3
NY, CUSTOMERID4
NY, CUSTOMERID5
LON, CUSTOMERID1
LON, CUSTOMERID2
LON, CUSTOMERID3
LON, CUSTOMERID4
LON, CUSTOMERID5

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bhavesh ShahLead Analyst
Top Expert 2010
Commented:
Hi,

Hope this is not homework. :-)


SELECT * FROM
(
SELECT Country, CustomerName, ROW_NUMBER() OVER(PARTITION BY Country ORDER BY Country DESC) AS 'Row'
from table1
)
WHERE Row <= 5

try this


 select customerid , City from Customers
where (
   select count(*) from Customers as f
   where f.City  = Customers.City
) <= 5

Author

Commented:
Ha ha - thanks for the humour Brichsoft

I will try these
awking00Information Technology Specialist
Commented:
See attached.
query.txt

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial