• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

help needed with Microsoft SQL Server syntax

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

0
Jimmy_inc
Asked:
Jimmy_inc
3 Solutions
 
Bhavesh ShahLead AnalysistCommented:
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

0
 
Pratima PharandeCommented:
try this


 select customerid , City from Customers
where (
   select count(*) from Customers as f
   where f.City  = Customers.City
) <= 5
0
 
Jimmy_incAuthor Commented:
Ha ha - thanks for the humour Brichsoft

I will try these
0
 
awking00Commented:
See attached.
query.txt
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now