A table contains list of customers and his city with other
details. Each customer has a unique number and the table
consists millions of data. Query is: I want to retrieve 10
customers from each city, no script, only from single query?




Answers were Sorted based on User's Feedback



A table contains list of customers and his city with other details. Each customer has a unique num..

Answer / grace

SELECT A.*
FROM dbo.T1 A
WHERE CustomerID IN
(SELECT TOP 10 CustomerID FROM dbo.T1 WHERE City=A.City)

Is This Answer Correct ?    24 Yes 6 No

A table contains list of customers and his city with other details. Each customer has a unique num..

Answer / asim

select id,addressid,city
from
(
SELECT ROW_NUMBER() OVER (partition by city Order by
addressid) ID,AddressID,City
FROM person.Address A
WHERE City IN
(select city from person.address group by City

) )a
where a.ID <11
order by City,ID

Is This Answer Correct ?    6 Yes 0 No

A table contains list of customers and his city with other details. Each customer has a unique num..

Answer / jyoti

CREATE TABLE #CustDetails
(
CustID INT IDENTITY(1,1),
CustName VARCHAR(10),
City VARCHAR(10)
)

INSERT INTO #CustDetails
SELECT 'A1','Pune' UNION ALL
SELECT 'A2','Pune' UNION ALL
SELECT 'A3','Pune' UNION ALL
SELECT 'A4','Pune' UNION ALL
SELECT 'A5','Pune' UNION ALL
SELECT 'A6','Pune' UNION ALL
SELECT 'B1','Delhi' UNION ALL
SELECT 'B2','Delhi' UNION ALL
SELECT 'B3','Delhi' UNION ALL
SELECT 'B4','Delhi' UNION ALL
SELECT 'B5','Delhi' UNION ALL
SELECT 'B6','Delhi' UNION ALL
SELECT 'B7','Delhi' UNION ALL
SELECT 'B8','Delhi' UNION ALL
SELECT 'C1','Mumbai' UNION ALL
SELECT 'C2','Mumbai' UNION ALL
SELECT 'C3','Mumbai' UNION ALL
SELECT 'C4','Mumbai' UNION ALL
SELECT 'C5','Mumbai'

SELECT
ID,CustName,City
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CITY ORDER
BY CustID DESC) ID,CustName,City
FROM
#CustDetails AS CD1
) A
WHERE A.ID < 3

Is This Answer Correct ?    4 Yes 0 No




A table contains list of customers and his city with other details. Each customer has a unique num..

Answer / rakesh ameta

create table newq(ids numeric(5),sname nvarchar(10),city nvarchar(10))

select * from newq where newq.ids in(select top 10 ids from newq x where newq.city=x.city)

Is This Answer Correct ?    0 Yes 2 No

A table contains list of customers and his city with other details. Each customer has a unique num..

Answer / ravi kumar ravuri

select * from Customer where Customer_ID in (select top 10
from Customer Where City_name in ( select City_Name from
Customer Group by City_Name))

Is This Answer Correct ?    0 Yes 4 No

A table contains list of customers and his city with other details. Each customer has a unique num..

Answer / abhijith

select top 10
from tablename
where city IN
(select distinct(city) from tablename)

Is This Answer Correct ?    2 Yes 6 No

A table contains list of customers and his city with other details. Each customer has a unique num..

Answer / abhijith

select top 10 *
from tablename
where city IN
(select distinct(city) from tablename)

Is This Answer Correct ?    1 Yes 5 No

A table contains list of customers and his city with other details. Each customer has a unique num..

Answer / sonal parekh

select Top 10 CustomerName, city from customer

Is This Answer Correct ?    5 Yes 31 No

Post New Answer



More SQL Server Interview Questions

how to find number of columns in a table in sql server 2000 and 2005 also

7 Answers   HCL, Virtusa,


What is normalization?

9 Answers  


if 3 duplicate records in a table,i want to delete 2 duplicate records by keeping 1 duplicate and 1 original as it is,how?

5 Answers  


Can we passed multiple recordset(set of records) using a Stored Procedure

2 Answers  


Why we are using the sql language?. What is the purpose of using this?

2 Answers  






how to find the second salary?

10 Answers   HCL, Prithvi,


how to update a null value field in sql server eg a table contains 3 fields id,name,salary and 3 records salary of 1 record is null i want update the nullfield 111 arun 300 112 ddd 200 113 ttt null i want to update table with add 100 to every record include null after updation the recrds should be 111 arun 400 112 ddd 300 113 ttt 100

6 Answers   HCL, ABC,


Why SQL Agent is used?

0 Answers   Abacus,


Delete duplicate rows without using rowid.

0 Answers   Nagarro,


WRITE A FUNCTION TO DISPLAY THE OUTPUT OF AN EXISTING TABLE RANGE LIKE COMMAM SEPERATED VALUES LIKE RANGE1,RANGE2,...

2 Answers   TVS,


You have modified 100 store procedures and want to replicate these changes from development to prodution, and production can have users using the Server/DB, how would you replicate without causing issues?

0 Answers  


WHAT OPERATOR PERFORMS PATTERN MATCHING?

2 Answers   CTS, CarrizalSoft Technologies,






Categories